Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Isolating a custom toolbar to a specific spreadsheet
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. |
#2
|
|||
|
|||
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. -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=475781 |
#3
|
|||
|
|||
"neopolitan" wrote
in message ... 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.. -- neopolitan ------------------------------------------------------------------------ neopolitan's Profile: http://www.excelforum.com/member.php...nfo&userid=611 View this thread: http://www.excelforum.com/showthread...hreadid=475781 |
#4
|
|||
|
|||
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/groups?th...5B41%40msn.com 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/mic...f7dde5a0477be9 or http://snipurl.com/iiye J.E. McGimpsey's code even suggests using the attached toolbars. Gilgamesh wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom toolbar and macros | Excel Discussion (Misc queries) | |||
How do I save a custom toolbar? | Excel Discussion (Misc queries) | |||
How can I attach a custom toolbar to only appear on one specific . | Excel Worksheet Functions | |||
I would like to delete a custom toolbar from the workspace. How? | Setting up and Configuration of Excel | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |