MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Closing a custom Toolbar


Posted by Greg Vincent on March 13, 2001 1:28 PM

How can I get a custom toolbar to close when I exit the
workbook. Also, is there a way to lock the tool bar so
it is only availble when the particular workbook it is tied
to is open?


Posted by Dave Hawley on March 14, 2001 12:10 AM

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