Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Customized Tool bar
As long as the file with the associated toolbar is opened, the toolbar will
remain in view regardless of other workbooks that may or may not be open at the time. You would need to write something additional into your project that fires a macro which determines if your template is the active workbook. Something like this: Private Sub Workbook_Activate() ActiveWorkbook.CommandBars("My Bar").Visible = True End Sub Private Sub Workbook_Deactivate() ActiveWorkbook.CommandBars("My Bar").Visible = False End Sub You'll need to work with this snippet a little because it won't execute in this form. Somebody else can chime in here for a rework but what ultimately is added needs to go under the "ThisWorkBook" code module in order for it to work. Harry-Wishes "Tami" wrote: Thank you so much for the explicit instructions...it was very helpful. We noticed that the toolbar stays visible when we toggle to a different workbook. Is there anyway around this as this toolbar is not applicable to the other workbook. "Harry-Wishes" wrote: 1) Hold down the ALT key and hit F11 to launch the VBE. 2) Under the Insert Menu, select "Module". This will open a blank window where you can paste your script. Tip: You will notice that on the left side of the VBE a project window containing a hierarchical view of your sheets (Sheet1, Sheet2, ect . . .), ThisWorkBook and the new module you just created. To be sure you are in the correct window, double-click on "Module" of this Project window and then on the right side of the VBE paste the code into this blank area. Harry-Wishes "Tami" wrote: Still not working....but not sure exactly what you mean by "not under Thisworkbook or Sheet" We copied and pasted the code you wrote below into a Module (by going to Macro/Create)...is that wrong? Here is our code... Sub Open_Tool_Bar() CommandBars("Assortment_Sheet").Visible = True End Sub Sub Close_Tool_bar() CommandBars("Assortment_Sheet").Visible = False End Sub "Harry-Wishes" wrote: Place these 2 subroutines below in a new module (not under Thisworkbook or Sheet). Notice the hyphen in the name of each sub as I omitted that in my earlier post. This one should work for you. Cheers! Harry Wishes Sub Auto_Open() CommandBars("My Bar").Visible = True End Sub Sub Auto_Close() CommandBars("My Bar").Visible = False End Sub "Tami" wrote: Wow that was a fast response...thanks! ok, so i pasted your macro in the "view code" of that sheet. i already have a lot of code in my "veiw code" so i pasted it at the top....a thin grey line appreared beneath each sub. its not working but its probably because i've pasted in the wrong place? "Harry-Wishes" wrote: From my experience, when you add a custum toolbar to an Excel file, the default appears to be that the toolbar gets associated with that file and will display even when the file is closed. I might try inserting an automacro that will hide the toolbar when the file is closed and insert another automacro that displays the toolbar when the file is opened. Here is a snippet below. Cheers! Harry-Wishes Sub AutoOpen() CommandBars("My Bar").Visible = True end Sub Sub AutoClose() CommandBars("My Bar").Visible = False end Sub "Tami" wrote: I have a two-part question. i've created a custom toolbar named "My Bar" I have attached to my file. When i close out of this particular file, "My Bar" remains open. HOw can i make this custom toolbar open only when in this file and be closed when i close out of it? next, many users will copy this file and Save As their own and populate with their own data. However, we may have new macro to add to that Bar or need to modify an existing macro on that bar, so it would be nice if it was "My Bar" was shared or in some central location?...is that possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Customized Sum | Excel Programming | |||
Customized Formula | Excel Discussion (Misc queries) | |||
Using of Customized Tool Bar in another file | Excel Discussion (Misc queries) | |||
Customized components | Excel Programming | |||
I am missing view tool bar from tool menu. | New Users to Excel |