Macro launched with keyboard shortcut breaks after a "File Open" action

This is really odd, so hopefully someone can explain why (and how to fix it).

I've created a macro that is run from an XLS of data. It presents a "file open..." dialog to enable the user to load up a Master Report file, then moves the XLS data into the Master report, does stuff to it and saves it onto the desktop as a new file.

This all works fine when launching the macro from VB, or using Alt-F8 and selecting it. But if I launch the macro using a keyboard shortcut (Ctrl+Shift+E) then it runs up to the point where it opens the selected Master Report, and then it just stops! No error, no obvious blip, but it does nothing after opening that file.

Please Login or Register  to view this content.

Huh?

I'm more stumped than an multiple-amputee stump-tailed macaque at a cricket match - anyone got any idea as to what on earth is going on here, and how I can make it launch from the shortcut?

Thanks in advance

Steve

Hi Tony

Thanks for the reply; it was using the MsgBox idea that got me to the point of blaming the File Open... section, so I'm glad I'd done something right.

Using your suggestions I dug deeper, and have pretty much confirmed that it is if the keyboard shortcut uses the SHIFT modifier then the File Open.. process terminates the macro without any error being logged. So the SHIFT must be interacting with the File Open... in some way.

I have added the full steps that I took to get to to this assumption below, but thought it best to summarise at the top of the message.

If anyone has any suggestions as to how to overcome this it would be useful to know, simply because all of the macros we use are called with Ctrl+Shift key combinations, and I like consistency.

KR

Steve


THE TL/DR bit

I started using the Debug.Print lines Tony suggested, and also put in (what I hope would be) an OnError handler too (new code lines in red):

Please Login or Register  to view this content.

then the final few lines had the error handler added:

Please Login or Register  to view this content.

As before, running the Macro with Alt-F8 and selecting works fully as expected, and Debugs as:
After sReport...
About to open workbook
Workbook is now open
Now have the workbook name stored
Now back to the dataset sheet
About to exit Sub


As before, running it with the keyboard shortcut stops on opening the second workbook, and debugs:
After sReport...
About to open workbook


So it appears File Open... is not passing control back to the macro as it doesn't Debug Workbook is now open


Then I set up the Macro as a button on my ribbon (Excel 2010), and that ran properly and logged fully, so it really does seem that the keyboard trigger is the cause of the trip-up.


So I tried different triggers, and discovered it was only when SHIFT was part of the shortcut that the problem occurred - Crtl+Shift+E and it stops, Ctrl+E and it runs to the end. It isn't the letter "E" either, as the same happened using Ctrl+Shift+J and Ctrl+J


So I can't see any way of getting any further into it, but it really looks like the Shift key is the problem here.

Hi Josie

I wasn't aware of that, thanks for the info. That's a bit of an esoteric action to have squirreled away, but I can see how it makes sense outside of my requirement and looks like that'll be the reason for my issue.

Have to say, though, that it does seem a bit odd that the SHIFT modifier must be explicitly defined as a macro keyboard shortcut (i.e. if your shortcut is normally Ctr+E you can't add SHIFT to invoke it with this block on), yet the SHIFT still retains that "block" property throughout the invocation of the macro. But, hey, it's Microsoft code, so anything is possible.


@Tony - Thanks for all your help, sir, but if it is the action as Josie mentioned above then I think we're at the end of the hunt. However you've shown me some really useful ways to debug my macros whilst ironing them out, and that is always much appreciated.

BTW, for completeness I did what you said anyway, and can confirm that the debug only recorded the proper line for opening he document by name:

About to open workbook "C:\Users\steve\Documents\Templates\Excel\130903-master.xltx"


So thanks both - I will mark this thread as SOLVED.

KR

Steve