Show/Hide a Custom Toolbar & Remove/Restore Excel's Toolbars

Show/Hide Custom Toolbars & Remove/Restore Excel's Native Toolbars

Got any Excel/VBA Questions? Excel Help

Hide & Restore Excel Toolbars in Excel VBA

One of the most exciting parts of Excel is perhaps its ability to be customized to show the end user something quite different. For example, many build their own custom toolbar and distribute them with their Spreadsheet. However, the one question that come up time and time again is 'how can I hide all of Excel's toolbars and show only mine when they are using my spreadsheet?' There are many ways to do this with some being extremely complicated (un-necessarily) through to totally flawed code which leaves the user Starting at a blank screen. The golden rule when/if changing any part of Excel is to make sure it all goes back to how it was when they are not in our specific spreadsheet. In other words, restore the users settings!

Attach Your Toolbar to the Workbook

For this example I will assume you have a custom toolbar called "MyToolbar" and you wish to hide ALL of Excel's built in toolbars and show only "MyToolbar". Before we do so though, it is VITAL that you attach your custom toolbar to the Workbook that will be using it. This will also stop users from being able to make changes stick and ensure you do not get the "The Macro could not be found", or where clicking a toolbar button opening up the Workbook so it can run the macro being clicked. To attach the custom toolbar and over-come these issues, follow the steps below;

1) Open the Workbook that should have the custom toolbar.

2) Right click on any grey unused part of any toolbar and choose "Customize".

3) On the "Toolbars" page check "MyToolbar" (or the applicable name) so it becomes visible.

4) Click "Attach" and then from the "Attach Toolbar" dialog select your toolbar and then click "Copy".

5) Click "Ok" then "Cancel" and it's done!

You should now be aware that, ANY changes made to your custom toolbar will not stick (between closing and re-opening the Workbook it's attached to) unless you first (before any changes) go back to the "Attach Toolbar" dialog and select your toolbar, this time from the right side ("Toolbars in workbook") and then "Delete". Now make any changes needed and then follow steps 1 to 5 again.

Coding the Toolbar Show and Restore

The 2 macros below are what can be used to show your toolbar, remove all native toolbars and most importantly restore them back when done;

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







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

The best way to enure these 2 macros are run at the correct time, is to place a Run Statement in the Workbook_Activate, Workbook_Deactivate procedures of the Workbook Object (ThisWorkbook). To get there quickly, right click on the Excel icon top left next to "File" on the Worksheet Menu Bar and select "View Code". In here place the code below;

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 sticking unless you have first deleted it (as shown above), made the changes and then attached it again. IMPORTANT: Do not run the Application.CommandBars("MyToolbar").Delete when the custom toolbar is NOT attached.