how can i make a custom command bar availble on opening and

[XiniX]

I made a workbook with a custom command bar. It is meant to be run on different computers only once in a while. How can i make the custom made command bar appear on opening of the workbook and disappear on closing the workbook...

Please help

[ This Message was edited by: XiniX on 2002-05-24 09:29 ]

[smozgur]

'ON ERROR RESUME NEXT avoids errors if commandbar doesnot exist.

Private Sub Workbook_Activate()
On Error Resume Next
ThisWorkbook.Application.CommandBars("Custom Toolbar").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
ThisWorkbook.Application.CommandBars("Custom Toolbar").Delete
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
ThisWorkbook.Application.CommandBars("Custom Toolbar").Visible = False
End Sub

Private Sub Workbook_Open()
Dim cbar1 As CommandBar
Dim comm1 As CommandBarButton

Set cbar1 = ThisWorkbook.Application.CommandBars.Add(Name:="Custom Toolbar", Position:=msoBarFloating)
cbar1.Visible = True
Set comm1 = cbar1.Controls.Add(Type:=msoControlButton)
With comm1
.Caption = "Control1"
.OnAction = "macroname1"
.FaceId = 23
.Style = msoButtonIconAndCaption
.Visible = True
End With
Set comm1 = cbar1.Controls.Add(Type:=msoControlButton)
With comm1
.Caption = "Control2"
.OnAction = "macroname2"
.FaceId = 3710
.Style = msoButtonIconAndCaption
.Visible = True
End With
Set comm1 = cbar1.Controls.Add(Type:=msoControlButton)
With comm1
.Caption = "Control3"
.OnAction = "macroname3"
.FaceId = 2188
.Style = msoButtonIconAndCaption
.Visible = True
End With
End Sub

I hope you like it. Code helps you to avoid working if relevant Document is not active (it might cause problems may be running codes within another document if you didnot use real name instead thisworkbook object)

Regards