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?

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?

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?

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?

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

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?

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?

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?

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" wrote:

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

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?