Customized Tool bar

[Tami]

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?

 

[Harry-Wishes]

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]

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]

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 Autpen()
CommandBars("My Bar").Visible = True
End Sub

Sub Auto_Close()
CommandBars("My Bar").Visible = False
End Sub

 

[Harry-Wishes]

Regarding the second part of your question: In Excel, you could distribute
copies of the file that has the custom toolbar to your users as a template.
Each user will be able to run the macros behind the toolbar buttons on their
own data from their local copy. I recently did this with a web project I
completed for my co-workers.

However, I would not include the automacros in the file because you need to
have that toolbar available for other workbooks (it's a long explanation).
Excel works differently when it comes to associating toolbars with Excel
files compared to Word files and it's custom toolbars. I found that the
association can break when the templates reach other users and they may have
to relink the buttons with the correct button the first time they use the
template on a new machine. You'll see what I mean when you actually do it.

Hope that helps.

Harry-Wishes

 

[Tami]

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]

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]

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.

 

[Tami]

hi harry, sorry to bother you again, but my co-worker was in my file earlier
today when the tool bar only had one button. since then, i have added several
other buttons. but when she opens my file, she still sees just one
button?...we've tried deleting the custom toolbar on her excel but the "old"
toolbar comes back when she opens my file...any suggestions?

 

[Harry-Wishes]

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

 

[Harry-Wishes]

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

 

[Harry-Wishes]

Which copy is your co-worker working from? Is it the one you updated?