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 NextWith Application.DisplayFullScreen = True.CommandBars("Full Screen").Visible = False.CommandBars("MyToolbar").Enabled = True.CommandBars("MyToolbar").Visible = True.CommandBars("Worksheet Menu Bar").Enabled = FalseEnd WithOn Error GoTo 0End Sub

Then, insert another module with the following code:

Sub RestoreToolbars( )On Error Resume NextWith Application.DisplayFullScreen = False.CommandBars("MyToolbar").Enabled = False.CommandBars("Worksheet Menu Bar").Enabled = TrueEnd WithOn Error GoTo 0End 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 SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)On Error Resume NextApplication.CommandBars("MyToolbar").DeleteEnd SubPrivate 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.