CODEWITHSUNDEEP
vbaexcelkeyboard-shortcuts
ForceMagic
ForceMagic

Reputation: 6378

Excel VBA: Macro hangs after opening a document when run via shortcut key, but runs perfectly from VB editor

I ran into a strange problem, I decided to assign a keyboard shortcut Ctrl + Shift + P on one of my routine in VBA. This routine is suppose to open an existing excel workbook, copy some informations and .SaveAs another name.

This actually runs fine when I hit Play in the Visual Basic Editor. However, when I use the keyboard shortcut inside Excel to run my code, it stop working after opening the document.

I reproduce the problem in this sample:

Public Sub WriteData()
    Dim templatePath As String
    Dim templateWorkbook As Workbook

    'it seems to hang on the following line
    Set templateWorkbook = Application.Workbooks.Open(templatePath)

    With templateWorkbook.Worksheets.Application
        .Range("B3") = "Employee name"
        .Range("B4") = "Employee first name"
        .Range("B5") = "Employee email"
    End With

    templateWorkbook.SaveAs(ThisWorkbook.Path & "Test")
    templateWorkbook.close
End Sub

I am using Excel 2007.

Upvotes: 4

Views: 7174

Answers (1)

ForceMagic
ForceMagic

Reputation: 6378

Apparently, after multiples google search about that particular problems, it seems that 

Application.Workbooks.Open(yourPath)

mixed with the Shift keyboard key in your shortcut is the problem. More precisely from this guy if you look at the last answer:

The shift key makes a vb script stop at any Open statement.

So you have two solutions

  1. Remove the Shift key from your shortcut and choose another one

  2. Instead of using .Open, you could use .Add which will do the same job, but the code won't hang. So if you do Application.Workbooks.Add(yourPath) it should behave properly.

It is funny though, that both Open and Add have the exact same behavior when you look at the screen while running the Macro.

Edit: I couldn't find any official link referring to that bug.

Upvotes: 7

Related Questions