Excel Macro Names
Hello Experts,
I probably need a little basic education on macro names.
I have a ‘Macro Menu’ macro that I wrote to help my ‘macro challenged’ users. This allows me to keep only one macro in our workbooks. This program will go out to the network where the latest version of all our macros reside, and get the names of all the macros and populate a menu for them to choose from. They can then select the macro to run and it will import the latest version and start it for them.
Sounds easy right ?
Well, I don’t know what causes the macro name to change in the ‘Tools, Macro’ window. Sometimes it is just the name of the Public sub, and sometimes it is: MyWorkbook!MyModule!MyProc
The selected macro will upload OK, but will not always run. The error I get is the macro can’t be found.
Please reply,
Believer
I probably need a little basic education on macro names.
I have a ‘Macro Menu’ macro that I wrote to help my ‘macro challenged’ users. This allows me to keep only one macro in our workbooks. This program will go out to the network where the latest version of all our macros reside, and get the names of all the macros and populate a menu for them to choose from. They can then select the macro to run and it will import the latest version and start it for them.
Sounds easy right ?
Well, I don’t know what causes the macro name to change in the ‘Tools, Macro’ window. Sometimes it is just the name of the Public sub, and sometimes it is: MyWorkbook!MyModule!MyProc
The selected macro will upload OK, but will not always run. The error I get is the macro can’t be found.
Please reply,
Believer
Hi believer
''''''''''''''''''''''''''
if I understand you correctly what you would need to do is have the menu bar deleted each time the file is closed so that the path name is not retained. Ie:
sub Auto_close()
application.CommandBars("y
end sub
Sub Auto_open()
application.CommandBars("y
end sub
Or/and in the workbook where the latest versions are do the same, or do not attach the toolbar.
antrat
''''''''''''''''''''''''''
if I understand you correctly what you would need to do is have the menu bar deleted each time the file is closed so that the path name is not retained. Ie:
sub Auto_close()
application.CommandBars("y
end sub
Sub Auto_open()
application.CommandBars("y
end sub
Or/and in the workbook where the latest versions are do the same, or do not attach the toolbar.
antrat
ASKER
Mixa,
I like your suggestion, and am pursuing it first. I want to see if it meets the needs of my users. They are low-end when it comes to the VBA environment.
I guess you could say that I am “Add-In Challenged” because I haven’t used them much. I was able to create an *.xla file with all my macros in it and save it to the network, and install it as an add-in.
My remaining question is how to call it. When I installed the other excel add-ins the menu automatically expanded to offer them. Do I need to create some custom menu item ?
Please Reply,
Believer
P.S.
Antrat, I haven’t forgotten about your suggestion. I am just pursing the add-in approach first.
I like your suggestion, and am pursuing it first. I want to see if it meets the needs of my users. They are low-end when it comes to the VBA environment.
I guess you could say that I am “Add-In Challenged” because I haven’t used them much. I was able to create an *.xla file with all my macros in it and save it to the network, and install it as an add-in.
My remaining question is how to call it. When I installed the other excel add-ins the menu automatically expanded to offer them. Do I need to create some custom menu item ?
Please Reply,
Believer
P.S.
Antrat, I haven’t forgotten about your suggestion. I am just pursing the add-in approach first.
You can create your own toolbar with button(s) to macros in your xla, and include it into xla workbook. So this toolbar will appear every time xla is loaded. The bad side of this is that toolbar will exist even after deinstalling xla (Excel makes a copy of such toolbars). In Auto_Open and Auto_Close macros of xla you can handle all additional toolbars (for example temporary ones existing only in current XL session) and extend XL menus, hot-keys, etc.
You can call macros of xla just in VBA code in VBA project of xls file with adding xla in References list (see VBE menu Tools / References) or with Run VBA statement if you don't want to have the reference to xla.
I suggest you place the macro with form that lists xla macros also into xla, then create new toolbar in XL, attach its button to that xla macro, then include toolbar into xla (see Custom Toolbar menu). After this users will only have to install xla in add-in manager (Tools / Add-Ins ... )
<MIXA />
You can call macros of xla just in VBA code in VBA project of xls file with adding xla in References list (see VBE menu Tools / References) or with Run VBA statement if you don't want to have the reference to xla.
I suggest you place the macro with form that lists xla macros also into xla, then create new toolbar in XL, attach its button to that xla macro, then include toolbar into xla (see Custom Toolbar menu). After this users will only have to install xla in add-in manager (Tools / Add-Ins ... )
<MIXA />
ASKER
Mixa,
Don’t mean to be difficult, but I am still having some problems. I have my *.xla file created.
It contains all my macros.
I created a custom toolbar with a button for each macro. How do I assign them to the macros in the *.xla ?
If I goto: View, Toolbars, customize and select the button. Then I choose assign macro. No macros are available unless they are in the particular workbook I have open ?
If I open the *.xla there is no worksheet available, and many menu items are grey ? Is this normal ?
Please reply,
Believer
P.S. I increased the points because this is a little harder then I first expected, at least for me anyway…
Don’t mean to be difficult, but I am still having some problems. I have my *.xla file created.
It contains all my macros.
I created a custom toolbar with a button for each macro. How do I assign them to the macros in the *.xla ?
If I goto: View, Toolbars, customize and select the button. Then I choose assign macro. No macros are available unless they are in the particular workbook I have open ?
If I open the *.xla there is no worksheet available, and many menu items are grey ? Is this normal ?
Please reply,
Believer
P.S. I increased the points because this is a little harder then I first expected, at least for me anyway…
The standard way of preparing xla:
1. Open source workbook with macros that will be saved as xla.
2. Create custom toolbar, for example "Custom1". Add buttons here. Assign macros to buttons, use macros from the current workbook (xls). If they don't appear in list of macros then it means they are declared as 'private' or with 'option private module'
3. Go to Toolbars tab of Customize Toolbars dialog. Press Attach button. Select your custom1 toolbar and copy it into current xls (right panel)
3. Go to VBE (Alt-F11). Recompile VBA project (menu Debug / Compile). It is not necessary, but after it Excel won't compile xla each time when it's loaded
4. Save xls file.
5. Save it as xla. xls is the source file, you will need it to produce next versions of xla. xla has some problems for modification. For example you have saved it as MyFile.xla
How it work on the user's PC:
1. Delete current toolbar "Custom1"
2. go to Excel menu Tools / Add-ins
3. If MyFile.xla doesn't appear in Add-Ins manager list, press Browse and locate the xla.
4. Exit to Excel, toolbar "Custom1" must appear, and pressing buttons on it will call xla macros.
That's all
There is next problem - "Custom1" toolbar will exist on user's PC even after deinstalling xla. You may delete it in Auto_Close xla routine (or Workbook_Close event). The other way is to create new toolbars / menus in Auto_Open xla routine with Temporary flag, so they will be destroyed automatically at the end of Excel session.
Hope it's that you want,
<MIXA />
1. Open source workbook with macros that will be saved as xla.
2. Create custom toolbar, for example "Custom1". Add buttons here. Assign macros to buttons, use macros from the current workbook (xls). If they don't appear in list of macros then it means they are declared as 'private' or with 'option private module'
3. Go to Toolbars tab of Customize Toolbars dialog. Press Attach button. Select your custom1 toolbar and copy it into current xls (right panel)
3. Go to VBE (Alt-F11). Recompile VBA project (menu Debug / Compile). It is not necessary, but after it Excel won't compile xla each time when it's loaded
4. Save xls file.
5. Save it as xla. xls is the source file, you will need it to produce next versions of xla. xla has some problems for modification. For example you have saved it as MyFile.xla
How it work on the user's PC:
1. Delete current toolbar "Custom1"
2. go to Excel menu Tools / Add-ins
3. If MyFile.xla doesn't appear in Add-Ins manager list, press Browse and locate the xla.
4. Exit to Excel, toolbar "Custom1" must appear, and pressing buttons on it will call xla macros.
That's all
There is next problem - "Custom1" toolbar will exist on user's PC even after deinstalling xla. You may delete it in Auto_Close xla routine (or Workbook_Close event). The other way is to create new toolbars / menus in Auto_Open xla routine with Temporary flag, so they will be destroyed automatically at the end of Excel session.
Hope it's that you want,
<MIXA />
ASKER
MIXA,
Thanks for the step by step instructions. I am awarding you the points because your have earned them. I must admit I still have a problem that I hope you will continue to help me with.
I was successful with steps 1 through 5.
In step 5, I saved the xls as an xla in a different location. Then I deleted the Custom toolbar. I added my new add-in, and everything seemed to go well. The problem I still have is the custom toolbar macros still point to the xls file. They have the entire path to it when you look at them (i.e. File.xls!MyMacro)
Any ideas…
Thanks,
Believer
Thanks for the step by step instructions. I am awarding you the points because your have earned them. I must admit I still have a problem that I hope you will continue to help me with.
I was successful with steps 1 through 5.
In step 5, I saved the xls as an xla in a different location. Then I deleted the Custom toolbar. I added my new add-in, and everything seemed to go well. The problem I still have is the custom toolbar macros still point to the xls file. They have the entire path to it when you look at them (i.e. File.xls!MyMacro)
Any ideas…
Thanks,
Believer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
believer,
I wrote a code to list the macros from any xls file.
The features include selecting a file and once the file is selected all the macros in that file would be listed on to this sheet.
My email Id is exuser21@hotmail.com
I did not see any of the above comments. I am all excited to put this comment, because it took me 3 days to write this. I tested this and it works excellent. You dont have to do anything except opening the xls that I send you.
I would send you the file you can check it for your self the code. The code is pretty complex.
vbkid
I wrote a code to list the macros from any xls file.
The features include selecting a file and once the file is selected all the macros in that file would be listed on to this sheet.
My email Id is exuser21@hotmail.com
I did not see any of the above comments. I am all excited to put this comment, because it took me 3 days to write this. I tested this and it works excellent. You dont have to do anything except opening the xls that I send you.
I would send you the file you can check it for your self the code. The code is pretty complex.
vbkid
Just save your workbook with macros as .xla (see Excel menu File / SaveAs / .xla) then install it on users' PC with add-in manager (Tools / Add-Ins / Browse). xla can be placed on network drive (or UNC), in this case all the users will use one copy of xla. Don't forget to answer "No" when add-in manager asks you to save .xla on the local PC.
Hope it can help,
<MIXA />