custom toolbar w/ macro

[Guest]

I have ceated two macros in a file that add and format lines to a
spreadsheet. For use of use, I've created a custom toolbar with a buttons
for the macros. The toolbar/macro works fine in the original file. It also
works if I "save as" customer1.xls. However, when I open the original file
and "save as" customer2.xls then run try to run the macro from the toolbar in
customer2.xls, the customer1.xls file opens and the macro runs in that file.
But - if I'm in customer2.xls, I can run the macro by going through
"tools/macros/macro/run" so it leads me to believe it is the toolbar, not the
macro. It's like there is a spreadsheet ownership problem with the toolbar.
Please help!

 

[Jim Rech]

If you had two of these workbooks open at the same time which workbook's
macros would the toolbuttons run? What you've done really isn't viable.
You should put the macros in a utility workbook and have the toolbuttons run
those macros. And the macros should be revised (if necessary) to work on
the active workbook, rather than a specific one, so they can be used with
any customer workbook.

--
Jim Rech
Excel MVP
|
| I have ceated two macros in a file that add and format lines to a
| spreadsheet. For use of use, I've created a custom toolbar with a buttons
| for the macros. The toolbar/macro works fine in the original file. It
also
| works if I "save as" customer1.xls. However, when I open the original
file
| and "save as" customer2.xls then run try to run the macro from the toolbar
in
| customer2.xls, the customer1.xls file opens and the macro runs in that
file.
| But - if I'm in customer2.xls, I can run the macro by going through
| "tools/macros/macro/run" so it leads me to believe it is the toolbar, not
the
| macro. It's like there is a spreadsheet ownership problem with the
toolbar.
| Please help!

 

[Guest]

Jim -
Thanks for the help. The toolbar macros are completely messed up if I have
two workbooks open. I've never heard of a "utility workbook" and how would I
go about placing the macros and/or custom toolbar in the utliity workbook.
Or how can I write a macro that can be used for any workbook or the active
workbook?

 

[Jim Rech]

how can I write a macro that can be used for any workbook

If the macros work on the "ActiveSheet" or the "ActiveCell" you should be
okay. They should never refer to a workbook by name or a worksheet by name
unless it's in every Customer workbook. You'll know if they don't work.

There is nothing special about it. Create a new workbook with only the
macros in it. Remove the macros from all Customer workbooks. Fix the
toolbuttons to run the macros in the utility workbook. None of the buttons
should be attached to any macro except in the utility workbook. If you've
attached the toolbar to the Customer workbook (I hope not) you must detach
it from each and resave. There must be only one copy of the toolbar!

--
Jim Rech
Excel MVP
| Jim -
| Thanks for the help. The toolbar macros are completely messed up if I
have
| two workbooks open. I've never heard of a "utility workbook" and how
would I
| go about placing the macros and/or custom toolbar in the utliity workbook.
| Or how can I write a macro that can be used for any workbook or the active
| workbook?
|
| "Darien" wrote:
|
| >
| > I have ceated two macros in a file that add and format lines to a
| > spreadsheet. For use of use, I've created a custom toolbar with a
buttons
| > for the macros. The toolbar/macro works fine in the original file. It
also
| > works if I "save as" customer1.xls. However, when I open the original
file
| > and "save as" customer2.xls then run try to run the macro from the
toolbar in
| > customer2.xls, the customer1.xls file opens and the macro runs in that
file.
| > But - if I'm in customer2.xls, I can run the macro by going through
| > "tools/macros/macro/run" so it leads me to believe it is the toolbar,
not the
| > macro. It's like there is a spreadsheet ownership problem with the
toolbar.
| > Please help!