route217
asked on
how to run a specific macro when excel is open
Hi Experts
I want to press alt and f8, irrespective of what version of excel I am in 2003 or 7...and I want to have I list of specific macro available to run...
how would I do this....I have all the macros...
so if file a.xls comes in via email (open file) I want to press alt and f8 and select macro sub abc() and run it...and if file b.xlsx arrives via email I want to open the file select alt and f8 and run macro sub cde()
And so on...
I want to press alt and f8, irrespective of what version of excel I am in 2003 or 7...and I want to have I list of specific macro available to run...
how would I do this....I have all the macros...
so if file a.xls comes in via email (open file) I want to press alt and f8 and select macro sub abc() and run it...and if file b.xlsx arrives via email I want to open the file select alt and f8 and run macro sub cde()
And so on...
ASKER
hi byundt
firstly, thanks for the excellent feedback. just one question how do I do"I suggest integrating a list of macros and userform in your Personal.xlsb workbook. You'll need to store it in the XLSTART folder for each of the versions of Excel that you use"
the attached workbook is great...
firstly, thanks for the excellent feedback. just one question how do I do"I suggest integrating a list of macros and userform in your Personal.xlsb workbook. You'll need to store it in the XLSTART folder for each of the versions of Excel that you use"
the attached workbook is great...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Do you have a Personal.xlsb workbook? no
I need to repeat your steps for 2003 and 2007...
its my first time at trying to do this...
thanks expert's
I need to repeat your steps for 2003 and 2007...
its my first time at trying to do this...
thanks expert's
Excel 2003 will use Personal.xls
Excel 2003 and later will use Personal.xlsb
You will likely have different XLSTART folders for each of your versions of Excel. The best way to find them is to record a macro and use the Immediate pane statement as previously described.
The Immediate pane is found in the VBA Editor on the right at the bottom of the window. If you don't see it, then use the View...Immediate menu item to display it.
If you type a statement (or paste it) in the Immediate pane, it will be executed immediately when you hit the Enter key. Make sure that the cursor is at the end of the statement before you hit Enter.
A question mark before a statement in the Immediate pane means to display the value returned on the next line. To display the filename and path for Personal.xlsb, use the following statement:
?Workbooks("Personal.xlsb" ).FullName
Assuming that your macros are already in another workbook, if you drag and drop a Module containing those macros from their current home to Personal.xlsb, then you will have them available in Personal.xlsb. This process is easier than copying and pasting the code.
Brad
Excel 2003 and later will use Personal.xlsb
You will likely have different XLSTART folders for each of your versions of Excel. The best way to find them is to record a macro and use the Immediate pane statement as previously described.
The Immediate pane is found in the VBA Editor on the right at the bottom of the window. If you don't see it, then use the View...Immediate menu item to display it.
If you type a statement (or paste it) in the Immediate pane, it will be executed immediately when you hit the Enter key. Make sure that the cursor is at the end of the statement before you hit Enter.
A question mark before a statement in the Immediate pane means to display the value returned on the next line. To display the filename and path for Personal.xlsb, use the following statement:
?Workbooks("Personal.xlsb"
Assuming that your macros are already in another workbook, if you drag and drop a Module containing those macros from their current home to Personal.xlsb, then you will have them available in Personal.xlsb. This process is easier than copying and pasting the code.
Brad
ASKER
Thanks for this works fine...
I have another question.... https://www.experts-exchange.com/questions/28173496/add-userform-to-excel-2007-to-return-result-tricky.html
I have another question.... https://www.experts-exchange.com/questions/28173496/add-userform-to-excel-2007-to-return-result-tricky.html
ASKER
hi byundt
totally stuck on this do nor have a clue. ...can you kindly assist. .
https://www.experts-exchange.com/questions/28174602/compile-error-variable-not-defined.html
totally stuck on this do nor have a clue. ...can you kindly assist. .
https://www.experts-exchange.com/questions/28174602/compile-error-variable-not-defined.html
Code to trap ALT + F8 and display a userform listing the macros
Open in new window
Code behind the userform:
Open in new window
MacroSelectorQ28170750.xlsm