Displaying Custom Toolbar in Specific Worksheet

[Thomas M]

Excel 2000

I've created a custom toolbar that I want to have displayed in only one
specific workbook. I copied the toolbar to the workbook, and added the
following routine to the ThisDocument module of the workbook (to ensure that
the toolbar is always visible when the workbook is opened).

Private Sub Workbook_Open()

Application.CommandBars("MyToolbar").Visible = True

End Sub

For some reason, the toolbar is visible in all workbooks. If I close it in
one workbook, it gets closed in all workbooks. If I make it visible in one
workbook, it will be visible in all workbooks, and will even be visible when
there are no workbooks opened.

I know how to do this in Word (add the toolbar to the specific document or
template, remove it from Normal.dot, and use the Word equivalent of the
above code in the Document_Open routine). I've been working on the
assumption that the solution in Excel will be similar to the solution in
Word, but it seems that toolbars are handled a bit differently in Excel.

How can I make the custom toolbar available to only a specific workbook.

--Tom

 

[Guest]

you can use vba to create the toolbar and then delete it when you are done
with it

 

[Bob Phillips]

Use a particular workbook test in the workbook activate application event

Put this code in the ThisWorkbook in the same workbook that creates the
toolbar

Private WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
If Wb.Name = "myBook.xls" Then
Application.CommandBars("MyToolbar").Visible = True
Else
Application.CommandBars("MyToolbar").Visible = False
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[GregR]

Thomas, you need to add a before_close event to the workbboook.
Application.CommandBars("MyToolbar").Visible = false

Greg

 

[Guest]

Hi Thomas,
You could use the WindowActivate and WindowDeActivate events of the Workbook
object to display and hide the toolbar.
1. Not sure how it is in Word, but in excel, a toolbar is linked to the
application, not a particular book, therefore when you hide the toolbar and
close the book, the toolbar is still there (just hidden; you can see it in
the View > Toolbars > Customize).
- In general, the best way is to create a toolbar on the fly
(programmatically when the book opens) and delete it when the book closes.
- Another thing you can do which would work well in you case (i believe):
create a toolbar, attach it to the book (menu View > Toolbar > Customize,
select the toolbar and click Attach, then attach it to the book). This will
store the toolbar with the book and everytime you open the book, the toolbar
popup. It will however NOT get hiden when the book closes, so you have to
delete the toolbar when the book closes (and make sure you delete it instead
of just setting its visibility to False).
2. Now to show/hide the toolbar based on which book is displayed, in the
ThisWorkbook module, use something like:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CommandBars("MyToolbar").Visible = True
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CommandBars("MyToolbar").Visible = False
End Sub

I hope this helps,
Regards,
Sebastien