The code below shows you how you can use Excels Workbook Events to run your code when the Workbook opens, Activates, Deactivates, Closes and Saves. The quickest way to get to Excels Workbook Events is to right click on the sheet picture (top left next to "File") and select "View Code". Then choose an event from the "Procedure" drop down list box.
For Excel 2000 you will need to select "Workbook" from the "Object" drop down list box first. All examples must be placed within the Private Module of the Workbook Object "ThisWorkbook" as described above. Unless stated otherwise! Hide all of Excels standard Menus and Toolbars and show only your Custom Toolbar.This code will decide if the user has closed your Workbook or simply Activated another. This code (unless changed) assumes you have a Custom Toolbar called "MyToolBar" which is attached to the Workbook.
Whenever the user closes or deactivates the Workbook, all Toolbars and Menubars will be restored as before. To attach your a Custom Toolbar go to View>Toolbars>Customize-Attach then Copy your Custom Toolbar to the Workbook.
'Module level declaration
Dim IsClosed As Boolean, IsOpen As Boolean
Private Sub Workbook_Activate()
'Show the Custom toolbar
IsClosed = False
If IsOpen = False Then
Application.ScreenUpdating = False
Run "HideMenus"
Application.ScreenUpdating = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True
If Cancel = True Then IsClosed = False
'Changed their mindEnd Sub
Private Sub Workbook_Deactivate()Application.ScreenUpdating = False
IsOpen = False
On Error Resume Next 'In case it's already gone.
If IsClosed = True Then 'Workbook is closing.
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Delete
End With
Run "ShowMenus"
Else 'They have only activated another Workbook
Run "ShowMenus"
End IfApplication.ScreenUpdating = True
End Sub
