Calling a macro from a separate .xla

[elgunter84]

I have two .xla macro's in front of me. One was written by a masterful VBA coder and so long as Excel is open, it is active in all workbooks. Any macro from the list of macros in the code is easily called by the corresponding hotkeys (CTRL+SHIFT+B etc.)

The second .xla macro is one that I recorded and can open up and see in Visual Basic Editor, and it works when I debug/step into it. But I can't call the macro while in the worksheet in question with the hotkeys I had assigned when initially recording it. Does that make sense?

Where were they keystrokes assigned on the first masterfully-crafted macro that the hotkeys automatically work in any and all active worksheets? How do I assign these keystrokes to my macro in question?

Thanks in advance for any help on the topic!

 

[wigi]

Can you locate the macro when you press Alt-F8?
There, with the options, you can set the hotkey of your choice.

 

[elgunter84]

I don't see the macro when I access that menu (Alt+F8), but neither do I see the other macros which I know I can call with their keystrokes. I wish the guy who had written the first macro weren't such a friggin' genius

 

[wigi]

Howcome, you don't see the macro you recorded when using Alt-F8?
Do you have a "Private Sub ...()" or do you use "Option Private Module" on top of the module?

Just to be sure, you look for "Macros in all open workbooks" when hitting Alt-F8?

 

[wigi]

Maybe follow the information here:

http://www.dailydoseofexcel.com/archives/2007/01/05/macro-shortcut-keys/

and see if you can find differences.

Can you get to the code of the add-in written by the genius?

 

[elgunter84]

So I believe I figured it out.

I had the code written in a .xla and so it doesn't appear in the list of Macros under the Alt F8 menu. You can, however, type the name of the sub in the list of Macro's and it will find it. You can then assign a hotkey to it.

For good measure, I used a .OnKey command as well to establish the names in the line of code.

The end result is that I can run the .xla and then any open workbook has access to these macros. This is because I am sending the macro to someone else, or else I would instruct them on embedding the code into their "Personal.xls" file.

Thanks for your input! Much appreciated

 

[wigi]

Thank your for your feedback and input !