CommandBarControl control question when using template

[Guest]

I have a template which contains a macro, and I added a Custom Menu Item to
the Standard toolbar to execute the macro. But when a new workbook is created
using the template, the new menu item cannot execute the macro. I know it has
to do with the new workbook not having the same name as the template, but I
can't figure out how to have the menu item point to the macro in the new
workbook rather than the template workbook. I.e. In the custom menu item
named 'Compare BOMs' in the template I set Assign Macro... to 'CompareBOMs'.
When I look at the Assign Macro... again, I see that it is set to
'CompareVBDWBOMs.xlt!CompareBOMs'. I then save the template and create a new
workbook with it. Now, Assign Macro... is set to
'CompareVBDWBOMs1!CompareBOMs' and clicking the 'Compare BOMs' menu item says
the macro 'CompareVBDWBOMs1!CompareBOMs' cannot be found. The macro
'CompareBOMs' is in the ThisWorkbook object.

 

[Jim Cone]

Put the macro code in your Personal.xls file and it will be available to any
workbook. Change your menu item to point to the procedure in Personal.xls.

Personal.xls is a hidden workbook that is created (by Excel) when a
macro is recorded for the first time in Excel. It is the storage place for
recorded macros and any macro added (pasted) into the module.
It opens (out of sight) whenever Excel starts.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"FClifton" <[email protected]>
wrote in message
I have a template which contains a macro, and I added a Custom Menu Item to
the Standard toolbar to execute the macro. But when a new workbook is created
using the template, the new menu item cannot execute the macro. I know it has
to do with the new workbook not having the same name as the template, but I
can't figure out how to have the menu item point to the macro in the new
workbook rather than the template workbook. I.e. In the custom menu item
named 'Compare BOMs' in the template I set Assign Macro... to 'CompareBOMs'.
When I look at the Assign Macro... again, I see that it is set to
'CompareVBDWBOMs.xlt!CompareBOMs'. I then save the template and create a new
workbook with it. Now, Assign Macro... is set to
'CompareVBDWBOMs1!CompareBOMs' and clicking the 'Compare BOMs' menu item says
the macro 'CompareVBDWBOMs1!CompareBOMs' cannot be found. The macro
'CompareBOMs' is in the ThisWorkbook object.

 

[Guest]

Jim,

Thanks for the assistance. I searched for a Personal.xls file, even
searching hidden and system files, but didn't find it. I am using Excel 2002,
does that make a difference? Also, I would like to be able to distribute this
template to others, so does your solution make that possible?

Thanks,
Frank

 

[Jim Cone]

Frank,
The Personal.xls file is created the first time you record a macro.
It remains on your system unless you delete it.
The file is normally stored in the Excel start folder.

Using Personal.xls allows you to keep a library of code that can
execute on your system only. Sending a workbook to someone else
only provides that workbook file to them. The Personal.xls file
remains on your system just like any other file.

I have had good luck "attaching" a custom Toolbar to a workbook
with a button(s) on it that run macros contained within the workbook.
That makes it easy to send a copy of the workbook to others.
The custom toolbar and code will be included with the copied workbook.
You can do a SaveAs on the workbook to create a new copy
as needed.

Creating a custom toolbar with buttons and attaching it to a workbook
can all be done manually. Tools | Customize gives you the bits, pieces
and parts to set it up.
If you go this route - please note...
if you want to change an existing attached toolbar,
the most reliable way is to remove the toolbar and create a new one.
Then attach the new toolbar and save the workbook.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"FClifton" <[email protected]>
wrote in message
Jim,
Thanks for the assistance. I searched for a Personal.xls file, even
searching hidden and system files, but didn't find it. I am using Excel 2002,
does that make a difference? Also, I would like to be able to distribute this
template to others, so does your solution make that possible?
Thanks,
Frank

 

[Guest]

Jim,

I used your idea of making a new toolbar, found some code snippets here that
worked for doing that on the fly, and now everything is working as I desire.
Thanks much for your help!

Frank