Jeff,
Here's my standard instructions/reply:
The best option is to create the commandbar on the fly, when the workbook is
opened, and delete the commandbar when the workbook is closed.
Follow these instructions and example code.
In the workbook's Thisworkbook object code module, place the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandbar
End Sub
Private Sub Workbook_Open()
CreateCommandbar
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
On Error GoTo NotThere
Application.CommandBars("My Bar").Visible = True
Exit Sub
NotThere:
CreateCommandbar
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.CommandBars("My Bar").Visible = False
End Sub
In a regular code module, place the following:
Dim myBar As CommandBar
Dim myButton As CommandBarButton
Sub CreateCommandbar()
On Error Resume Next
DeleteCommandBar
Set myBar = Application.CommandBars.Add("My Bar")
With myBar
.Position = msoBarTop
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Hello"
.Style = msoButtonIcon
.FaceId = 137
.Enabled = True
.OnAction = "SayHello"
End With
End With
End Sub
Sub DeleteCommandBar()
'Delete the commandbar if it already exists
On Error Resume Next
Application.CommandBars("My Bar").Delete
End Sub
Sub SayHello()
MsgBox "Hello there"
End Sub
You can add as many buttons or other menu items as you like.
HTH,
Bernie
MS Excel MVP
