Personal Macro Workbook mystery

[Misca]

I've got a macro stored in my Personal Macro Workbook. What it does is basically open a workbook and add a new row to the existing table.

It works perfectly if I run it using any other way but using the shortcut keys. The macro still opens the workbook using the shortcut keys but it doesn't make any changes to the worksheet.

I have no idea why this happens or how to solve this. Any help would be appreciated for I'd prefer to use the keys to run it.

 

[alansidman]

Post your Macro here for analysis. I suspect it does not reference the appropriate workbook.

 

[Misca]

I'd rather not post the code here for there's some sensitive data on it. Besides, I can't see how the references could be the issue here: if they were the macro should return an error or at least the same problem would exist regardless of how you run the macro.

This strange behavior only happens when using the shortcut keys. If I run it from the editor or a button or using an icon in the QAT everything works just fine. The only mystery is the shortcut keys. My current shortcut for the macro is Ctrl + Shift + "E" but the same problem exist with all the keystrokes I've tried it with. Even then it opens the correct workbook and selects the correct cell in it but it just doesn't make changes to the worksheet unless I run it using any other method.

Actually, I even tried it running it from another macro: Works like a charm if I run that macro from the VBA editor or a button but original macro still makes no changes to the worksheet if I fire the helper macro using shortcut keys.

 

[Misca]

Decided to write a simplified version of the original code. The code behaves just the same way as the original code: Works just fine unless fired using a keyboard shortcut.

Sub Testing()

Dim WB As Workbook
Dim c As Range

Set WB = Workbooks.Open("C:\TMP\Test1.xlsx")

With WB
    With .Sheets(1)
        Set c = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
        With c
            .Select
            .Offset(, 1).Value = Date
        End With
    End With
    .Save
End With

End Sub

Actually, when I fired this using the keyboard shortcut, I noticed this didn't even select the c range. I thought the original code did but turns out it didn't either.

 

[Fluff]

Get rid of the .Select line
a) you don't need it
b) unless sheets(1) is the active sheet the code will fail.

 

[Misca]

I kinda need the .Select line for the date is not the only thing I'll be adding to the worksheet. I want the macro to add the dates & default values to the row and select the cell where my input is needed because it should be able to do all those things easily. Usually I'd add the .Activate line before selecting a cell but these workbooks only have one worksheet.

I'm happy with the code itself but I just can't understand how come the keyboard shortcut messes up the code so that it skips all the rows where it's supposed make changes to the worksheet.

 

[Fluff]

In the code you have posted the .select line does absolutely nothing, other than risk causing problems.

 

[Misca]

Like I said earlier I didn't want to post the original code but made a simplified version of it. The .Select line is really not needed here as you said, but all I'm really interested in is what is so special about running the code with a keyboard shortcut. The same piece of code skips most of the code without errors or anything when fired though the keyboard shortcut but performs every line as it should if fired any other way.

I even tried firing the macro with worksheet events and it did everything I wanted it to do. But whenever I run it with the keyboard shortcut it only does the first few lines - till the workbook is open - and skips the rest.

I added a msgbox to the end to make sure this was the case and I never saw the msgbox when run with a keyboard shortcut but always with any other way.

 

[Fluff]

Do you have any error handlers in your code?

 

[Misca]

Nope, none.

I don't think the code is the problem because the code works 100 % whenever run by any other method. The keyboard shortcut seems to sparkle something mystical to the macro behavior so that it makes it skip most of the code. No errors or anything.

It looks like it's not even related to the Personal Macro Workbook because I can copy the code to any workbook and it runs perfectly as long as I trigger it any other way but using the keyboard shortcuts.

And what's even more mysterious is the keyboard shortcut magic is carried over from one macro to another because if I trigger it from another macro, the original code works like a charm unless the macro that triggers the original macro is fired using another keyboard shortcut.