keeping a toolbar attached to a workbook

[Paul James]

I've created a custom toolbar and used the Attach Toolbars dialog to attach
it to the workbook in which it was created. However, I notice that
sometimes the toolbar remains in Excel after I close the workbook.

What I would like is to have that particular custom toolbar only appear when
that workbook is open, because there is no use for it in any other
workbooks. Is there a way to make sure that toolbar is only visible when
that particular workbook opens?

Thanks in advance,

Paul

 

[Jean-Yves]

Hi Paul,

Use the ThisWorkbook module :
Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars("YourToolbar").Visible = True
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("YourToolbar").Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("YourToolbar").Delete
End Sub
Regards,
Jean-Yves

 

[Paul Ponzelli]

Hi Jean-Yves,

I added your procedures to the This Workbook module, and they work just
fine. It also seems they solved another problem I was having. Before I
added your code, when I tried to run any of the commands from the toolbar,
it tried to open another workbook, which was an earlier version of the
current workbook, but with a different name. If it found the workbook, it
would try to use the (old) VBA code in the old workbook. If it couldn't
find the older version of the workbook, it would produce the following error
message:

"A document with the name of 'Div 1.2 Program 3.xls' is already open. YOu
cannot open two documents with the same name, even if the documents are in
different folders.
To open the second document, either close the document that's currently
open, or rename one of the documents."

Evidently, attached toolbars somehow (mis)remember the name of the workbook
to which they were originally attached. I don't know why your code solved
this problem, but it seems to have solved this one also.

Any idea why attached toolbars exhibit this behavior?

In any event, thanks for giving me the code to solve those two problems.

Paul