Hi Mark -

Use a macro to build your commandbar. It's fast, pretty reliable, and
easier to change that if you attach it.

Put these into the ThisWorkbook code module of the workbook. The
Workbook_Open and _BeforeClose event procedures build and destroy the
commandbar, the _Activate and _Deactivate procedures show and hide it.

Option Explicit

Private Sub Workbook_Open()
Create_Menu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Delete_Menu
End Sub

Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars(MENU_NAME).Visible = True
On Error GoTo 0
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars(MENU_NAME).Visible = False
On Error GoTo 0
End Sub

Here's some sample code to build a commandbar. Put it into a regular
code module in the same workbook.

Option Explicit

Public Const MENU_NAME As String = "My Menu"

Sub Create_Menu()
Dim MyBar As CommandBar
Dim MyPopup As CommandBarPopup
Dim MyButton As CommandBarButton

Delete_Menu

Set MyBar = CommandBars.Add(Name:=MENU_NAME, _
Position:=msoBarFloating, temporary:=True)

With MyBar
.Top = 125
.Left = 850

Set MyPopup = .Controls.Add(Type:=msoControlPopup)
With MyPopup
.Caption = "Popup 1"
.BeginGroup = True
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Button 1a"
.Style = msoButtonCaption
''' msoButtonAutomatic, msoButtonIcon, msoButtonCaption,
''' or msoButtonIconandCaption
.BeginGroup = True
.OnAction = ThisWorkbook.Name & "!Macro1a"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Button 1b"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = ThisWorkbook.Name & "!Macro1b"
End With
End With
Set MyPopup = .Controls.Add(Type:=msoControlPopup)
With MyPopup
.Caption = "Popup 2"
.BeginGroup = False
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Button 2a"
.Style = msoButtonCaption
.BeginGroup = True
.OnAction = ThisWorkbook.Name & "!Macro2a"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Button 2b"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = ThisWorkbook.Name & "!Macro2b"
End With
End With
.Width = 100
.Visible = True
End With

End Sub

Sub Delete_Menu()
On Error Resume Next
CommandBars(MENU_NAME).Delete
On Error GoTo 0
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Mark Reynolds wrote:

Hi All,
I have a question for the Excel experts. How do you get custom
commandbars to only open with a specific workbook, then close when the
workbook is closed? I already use an auto_open macro for certain
tasks. Would I insert commanbar = true statements into the macro? If
I did this, how would I get the commandbar(s) to automatically close
when I exit the workbook? Thanks for any help.
Mark