I'm not quite sure what it is I'm working with here. I have recently inherited a masterpiece macro from someone I am replacing at work. The macro runs beautifully, but there are complexities that exist because I have no idea how it was originally written. I had an idea to improve on the macro, and have the code now to implement into the macro but there's a rub... I can't figure out how to call my macros with their respective hotkeys like the existing one does already.
The structure of the macro is such that once it has been opened, it populates any instance of excel that's open and won't shut down until all instances of excel are closed. Ctrl+Shift+B will run the macro in any window of excel that you happen to be on. I've learned how to use an application.onkey within the workbook tab of VBA editor, but I have to copy the application.onkey code to each new sheet I open and save in order for the macro to work in the newly opened sheet.
Any idea of where I'd look to find the coding on this master macro that I'm dealing with in order to implement more macro-calling code? I hope this makes sense, as I'm spinning my wheels in air now...
Thanks in advance for the help, and apologize for the length of the explanation.
Last edited by soran2484; 08-18-2010 at 03:11 PM. Reason: solved
Perhaps the code is in the PERSONAL.XLS workbook. You could do the same to put in a macro that would then be available to all workbooks.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I'm having no luck identifying where my PERSONAL.XLS file is. The file itself is an xla, and once you have opened it, it shows up on my VBA editor as its own project. I've saved a public application.onkey sub series into it, but that's not the ticket.
I've written the macros into the end of the master macro, but can't figure out how to call them with the hot keys I desire. There is nothing apart from what I've put into the workbooks in VBA editor to call the macros that originally existed. Any ideas on how/where I may find the personal.xls file to verify whether that is or isn't the issue? I've done a system search for personal.xls with search results coming back empty.
Thanks again your help Jerry
In XP, Personal.xls (if one exists) lives in C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart\
If you don't see it as a project in the VBE, you don't have one.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Takes me back to square one.
If you wanted to write an xla file, that when opened went effective in all workbooks of excel, how would you go about it?
I'm flat out confuzzled
For the basics of creating an add-in, see http://www.fontstuff.com/vba/vbatut03.htm and http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks for the links. I'll get to reading, and report back with my findings
Good information on xla's, but I'm still not understanding where to code in the shortcut keys. Any help on how to reprogram shortcut keys in xla's? I've explored the onkey function from the workbook idea, but that doesn't persist to all open workbooks. I know it has to be possible because I have an xla in my hands that works just that way. Is there anything apart from VBA Editor that is used to modify xla's?
To assign a shortcut to a sub that takes no arguments,
o Alt+F8 to open the Macros dialog
o Type in the name of the sub (the Run buttons and others will enable)
o Press Options and follow your nose from there
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Wow that was simpler than I could have imagined!
A hopefully simple followup question:
If I want to use multiple keys apart from CTRL, for instance Shift+CTRL+ALT+a, where can I modify that? The Alt-F8 menu only gives me CTRL + as an option...
Thanks Shg
(Looks like I can actually do CTRL + Shift by holding down shift and then hitting the key, but Alt isn't working)
Last edited by soran2484; 08-18-2010 at 03:08 PM. Reason: discovery
Ctrl and Ctrl+Shift are all you get.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Fair enough. Thanks so much for the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks