I suggest that the file creates its own tool bar usibng
the auto_open or workbook_open event, and removes th ebar
with the close events. that way the app controls the bar.
here's some example code that I sent in an earlier mail.
place in a standard modle. call the AddMenu proc from the
open event and call the killmenu proc from the colse
event.
Sub AddMenu()
Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim ctrlSubItem As CommandBarButton
KillMenu
Set ctrlMain = CommandBars("Worksheet Menu
Bar").Controls.Add _
(Type:=msoControlPopup, _
temporary:=True)
With ctrlMain
.Caption = "&Analysis"
Set ctrlItem = .Controls.Add
(Type:=msoControlButton)
With ctrlItem
.Caption = "&Initialse Test"
.OnAction = "subInitialise"
End With
Set ctrlItem = .Controls.Add
(Type:=msoControlPopup)
With ctrlItem
.Caption = "&Explore Test"
.BeginGroup = True
Set ctrlSubItem = .Controls.Add
(Type:=msoControlButton)
With ctrlSubItem
.Caption = "Type &1"
.OnAction = "sub1"
End With
Set ctrlSubItem = .Controls.Add
(Type:=msoControlButton)
With ctrlSubItem
.Caption = "Type &2"
.OnAction = "Sub2"
End With
End With
Set ctrlItem = .Controls.Add
(Type:=msoControlButton)
With ctrlItem
.Caption = "&Plan Test"
.BeginGroup = True
.OnAction = "sub3"
End With
End With
End Sub
Sub KillMenu()
Dim cmdbar As CommandBar
On Error Resume Next
Set cmdbar = CommandBars("Worksheet Menu Bar")
cmdbar.Controls("&Analysis").Delete
On Error GoTo 0
End Sub
Patrick Molloy
Microsoft Excel MVP
