Isolating a custom toolbar to a specific spreadsheet

[Gilgamesh]

I've created some macros and a custom toolbar to activate them but I want
these to only exist in a single spreadsheet.
I used the Customize->Attach feature to move my toolbar into the relevant
spreadsheet and then deleted it from the main list.

The problem I'm getting is that when I open the spreadsheet with the toolbar
it seems to copy it back into the main Excel app and it is then available
for all spreadsheets I open. Clicking on the buttons when another
spreadsheet is open then opens my customised one to run the macros.

I don't want this situation. When I close my customised sreadsheet file I
want the toolbar to disappear and not affect anything else.

I'm using Excel 2003, and helpful suggestions would be appreciated.

Thanks.

 

[neopolitan]

Here is a way to do what you want, there may be others.

Instead of creating a toolbar with all your button on it( it wil
always reside in the top of the screen independent of what spreadshee
file you have open); in the file where you want your macro buttons t
reside, create a textbox for each one - you can assign a macro to
textbox. The text boxes can only reside within the spreadsheet (the
sit on top of the cells so you will be losing access to some cells).
You can enter what ever text you want to identify the macro and eve
color the text and the background to make it stand out from the cells.

Make sure that when you save your macros (for this workbook only) tha
you specify "For this workbook". If you already have macros created i
a module under the Personal.xls workbook; you will have to copy an
paste the code into new modules in the specific workbook you want the
in and then delete them from the Personal.xls module list

 

[Gilgamesh]

Here is a way to do what you want, there may be others.

Instead of creating a toolbar with all your button on it( it will
always reside in the top of the screen independent of what spreadsheet
file you have open); in the file where you want your macro buttons to
reside, create a textbox for each one - you can assign a macro to a
textbox. The text boxes can only reside within the spreadsheet (they
sit on top of the cells so you will be losing access to some cells).
You can enter what ever text you want to identify the macro and even
color the text and the background to make it stand out from the cells.

Make sure that when you save your macros (for this workbook only) that
you specify "For this workbook". If you already have macros created in
a module under the Personal.xls workbook; you will have to copy and
paste the code into new modules in the specific workbook you want them
in and then delete them from the Personal.xls module list.

If all else fails I will look at doing this as a workaround.
You have pointed out the advantage of toolbars in that they are available in
every worksheet within the file in question whereas with this option I'll
have to copy the textbox onto every sheet..

 

[Dave Peterson]

First, I wouldn't attach the toolbars to the workbook. I'd build that toolbar
each time the workbook opened (and delete it each time the workbook closed).

Here's how I do it:
http://groups.google.co.uk/[email protected]

If you want to add items to the worksheet menu bar, you can use John
Walkenbach's menumaker:
http://j-walk.com/ss/excel/tips/tip53.htm

And after you have that done, if you really want to make sure that the macro
only works against worksheets in that workbook, you have at least a couple of
choices.

#1. Modify the code for each macro:
near the top of each subroutine
if activeworkbook.name <> thisworkbook.name then
'msgbox "no, no, no"
exit sub
end if

#2. Use some workbook events to hide/delete the toolbar whenever you change
workbooks.

workbook_deactivate and workbook_activate would seem to fit nicely.

I went to google groups and searched for workbook_deactivate. I sorted the
results by date and found this thread:

http://groups.google.co.uk/group/mi...e+group:*excel*&rnum=3&hl=en#14f7dde5a0477be9

or

http://snipurl.com/iiye

J.E. McGimpsey's code even suggests using the attached toolbars.