Here is something better that will display the custom toolbar only when you
are using the template and hide it when you select other workbooks. Paste
this under the "ThisWorkbook" code module as I mentioned in my last post.

Harry-Wishes


Private Sub Workbook_Activate()
On Error Resume Next
With Application
.CommandBars("My Bar").Visible = True
End With
On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
.CommandBars("My Bar").Visible = False
End With
On Error GoTo 0
End Sub


"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?