Open and close custom toolbar with workbook

[Mark J]

Hi, I have created a custom toolbar (Called Menu Bar) with 5 macros assigned
to buttons. I want to open and close this with my workbook every time, but
not with other workbooks. I have attached it to my workbook, but need to add
some VB I think. Can anyone help please?

 

[paul.robinson]

Hi
In the ThisWorkbook code module put in this

Private Sub Workbook_Activate()
Call Add_Menu
End Sub

Private Sub Workbook_Deactivate()
Call Remove_Menu
End Sub

Private Sub Workbook_Open()
Call Add_Menu
End Sub

In a normal module put your code to create and destroy the menu e.g.

Public Sub Add_Menu()
Dim cbWSMenuBar 'Variable name for main Worksheet Menu Bar
Dim muCustom As CommandBarControl 'menu item on main Toolbar
Dim iHelpIndex As Integer 'item number of Help menubar item

Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
'If Excel crashed while last opened so that Before_Close() event
didn't happen
'the Timetable menubar may still exist. So delete it just in case
On Error Resume Next
cbWSMenuBar.Controls("myMenu").Delete
On Error GoTo 0

iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup,
before:=iHelpIndex)

With muCustom
.Caption = "myMenu"
'add stuff
End With
Set cbWSMenuBar = Nothing
Set muCustom = Nothing
End Sub

Public Sub Remove_Menu()
Dim cbWSMenuBar As CommandBar

On Error Resume Next 'Incase it has already been deleted
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("myMenu").Delete
Set cbWSMenuBar = Nothing
End Sub

regards
Paul