Coding the Toolbar Show and Restore

The two following macros will display your toolbar (MyToolbar), remove all native toolbars and, most importantly, restore them for the user when your spreadsheet is closed.

To insert a standard module:

Right-click on the sheet name, select View Code → Insert → Module to insert a Standard Module, and paste the following code:

Sub RemoveToolbars( )
    On Error Resume Next
	With Application
	    .DisplayFullScreen = True
	    .CommandBars("Full Screen").Visible = False
	    .CommandBars("MyToolbar").Enabled = True
	    .CommandBars("MyToolbar").Visible = True
	    .CommandBars("Worksheet Menu Bar").Enabled = False
	End With
    On Error GoTo 0
End Sub

Then, insert another module with the following code:

Sub RestoreToolbars( )
    On Error Resume Next
	With Application
	    .DisplayFullScreen = False
	    .CommandBars("MyToolbar").Enabled = False
	    .CommandBars("Worksheet Menu Bar").Enabled = True
	End With
    On Error GoTo 0
End Sub

You aren't quite finished yet! You still need to make sure that both macros run at the correct time, so you'll have to place a Run statement in the Workbook_Activate and Workbook_Deactivate procedures of the Workbook object (ThisWorkbook). Right-click on the Excel icon (on the top left, next to File on the worksheet menu bar), select View Code, and insert the following code:

Private Sub Workbook_Activate( )
    Run "RemoveToolbars"
End Sub

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

Private Sub Workbook_Deactivate( )
    Run "RestoreToolbars"
End Sub

Note the deletion of the custom toolbar when the workbook closes. This is what prevents any changes from sticking unless you have first deleted it (as shown previously), made the changes, and then attached it again.

Do not run the Application.CommandBars("MyToolbar"). Delete when the custom toolbar is not attached.