Show/Hide a Custom Toolbar & Remove/Restore Excel's Toolbars
Category: [Excel] Demo Available
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.
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.