"Scope" of subroutines run via a shortcut 1

[Deniall]

I am having some difficulties with what I think is called the "scope" of VBA subroutines in cases where these subroutines are invoked via a "Ctrl-x" style shortcut.

This arises in a workbook I developed for a client.[ ] I never envisaged that the client's staff might have two of the workbooks open simultaneously, but it turns out that they do this all the time.[ ] The two workbooks have different names of course, and they contain different data.[ ] But all the formulae, the worksheet names, and the macros (and the macros' shortcuts where applicable) are identical.

The macros also rely on several Public variables to function correctly, and it was misbehaviour in the values of these variables that exposed the problem.[ ] My first approach to fixing things was to include a "Option Private Module" statement at the top of all my modules.[ ] This improved things, and I thought I was out of the woods.[ ] But then I realised that while all the subroutines worked correctly within the VBA environment, the shortcuts no longer worked because subroutines in private modules are not visible to the spreadsheet environment.

My next idea was to add a small public module, containing one-line subroutines that simply call the appropriate target subroutine in the private module.[ ] These trivial subroutines were assigned the required shortcuts.[ ] Thus to run a subroutine called Fred in a private module I created in the public module a subroutine
[ ][ ][ ][ ]Sub Fred_()[ ][ ][ ]'Note the underscore in the name
[ ][ ][ ][ ][ ][ ][ ]Call Fred
[ ][ ][ ][ ]End Sub
and assigned the shortcut Ctrl-Shift-F to this.

This does not work properly either (when multiple worksheets are open).[ ] But playing around with this approach has helped me to get closer to the root of the problem.[ ] Suppose I fire up Excel, then open Spreadsheet1, then open Spreadsheet2.[ ] The order of opening seems critical.[ ] If I am working on Spreadsheet1 and run the shortcut, the correct version of Fred_ is invoked, and this in turn runs the correct version of Fred.[ ] However if I switch to working on Spreadsheet2 then run the shortcut it invokes the wrong version of Fred_ which in turn runs the wrong version of Fred.

Further experimentation revealed that the problem seems to come from a problem with ThisWorkbook.[ ] When I am working on Spreadsheet1 both ThisWorkbook and ActiveWorkbook are set to Spreadsheet1 when I run the shortcut, but when I am working on Spreadsheet2 then ThisWorkbook is (wrongly?) still set to Spreadsheet1 while ActiveWorkbook is (correctly?) set to Spreadsheet2.

Before I "solve" this problem by putting a test for ThisWorkbook equalling ActiveWorkbook in subroutine Fred_ (and aborting the operation if the test fails), does anyone know what is going on here?[ ] And more importantly does anyone know how to fix it?

In case it helps, I will upload a "matching pair" of very simple spreadsheets that exhibit the problem.

Thanks for reading this far.

 

[combo]

There is no problem with ThisWorkbook: it's always reference to the workbook that contains vba project with macro calling it. In your case its a workbook that contains hotkey's macro. Seems that excel assigns the shortcut to the first opened file that defines the shortcut, no matter what workbook is active.
You could use Workbook_Activate() and Workbook_Deactivate() event procedures to reassign macro with Application.OnKey instead. Having the same macro names, vba will set macro from the same workbook where the event procedure is.



combo

 

[Deniall]

Thanks, Combo.[ ] That gives me a useful new perspective on the problem.[ ] I won't be able to explore things further for another week or so, as I am about to go away to a place that does not have any internet access.[ ] (Yes, such places do still exist, although they are increasingly rare.)