Closing a custom Toolbar

Hi Greg

To do this you will first need to Attach your Custom Toolbar to your Workbook.
Go to View>Toolbars>Customize
On the "Toolbars" tab click "Attach"
Select Your Custom Toolbar and click "Copy"
Click Ok then Ok again.

Now your Custom Toolbar is attached to the Workbook. This means it will always become visible whenever you open the Workbook.

Now you will need some code placed in the Workbook module to Hide/Show and Delete the Custom Toolbar.

Right click on the sheet picture, top left next to "File" and select "View Code"
Paste in this code:

Dim IsClosed As Boolean

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = True Then
IsClosed = False
Else
IsClosed = True
End If
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
If IsClosed = True Then
Application.CommandBars("MyCustomBar").Delete
Else
Application.CommandBars("MyCustomBar").Visible = False
End If
End Sub

Change "MyCustomBar" to your Custom Toolbar name.
Push Alt+Q to return to Excel.
Save.

Whenever you Close your Workbook the Toolbar will not be accessible at all.
Whenever you switch to another open Excel file the Toolbar will become hidden. As soon as you switch back to your Workbook the Toolbar will be visible.

If you make any changes to your Toolbar that you want saved you will first need to:
Go to View>Toolbars>Customize
On the "Toolbars" tab click "Attach"
Select Your Custom Toolbar and click "Delete"
Click Ok then Ok again.
Make your changes then go back and Attach it again.

DaveOzGrid Business Applications