The ExcelHelpDesk Support team received the following request for help
Problem – Replicate CTRL+F1 to hide the Excel Toolbar
I know to press Ctrl + F1 to Hide the Toolbar at the top of a workbook, but how do I ensure the workbook retains this view for the end user?
There are a number of options to solve this problem, the simplest approach we found for this request was the combination of the “SendKeys” function and the Workbook_Activate procedure.
Basically we created a routine that would Hide the Toolbar each time the workbook was activated. Then when the workbook was closed or de-activated it would reset the Toolbar to the original state.
This feature can be useful if you have a workbook that displays a large amount of information on the screen and the number of available rows in the window needs to be maximised.
Step 1 – Create Macro to Hide or Show Toolbar
A simple macro stored in the Workbook_Activate procedure will Hide the Toolbar if it is currently visible. To check if the Toolbar is currently visible we are using the “Height” property for the “Ribbon” Toolbar. If it is greater than 60 then we know that it is visible and needs to be hidden.
To Hide the Toolbar we are using the “SendKeys” function. This function essentially sends the required string of keys to the active window as though they were being typed into the keyboard. In this case we want the keys CTRL + F1 sent to the keyboard. “SendKeys” can be used for many other purposes where a keyboard entry needs to be replicated.
If Application.CommandBars("Ribbon").Height > 60 Then
Note: This Macro will only work in Excel 2007 and above workbooks. It is not compatible for any earlier versions of Excel.
Step 2 – How to access Workbook_Active procedure
To access the Workbook_Activate procedure for a workbook simply open the VBA editor within Excel. Then double click on the “The Workbook” object, at the top of the code window you will see a drop down option with “General” selected.
Simply change this to Workbook and then the drop down beside that will provide access to the Workbook_Activate procedure. Once you select that it will create the procedure for you in the code window ready for entry.
Note: You can also just copy in the code supplied in Step 1 to the Workbook code module
Step 3 – Now to Show the Toolbar when Workbook is not Active
To reverse the Toolbar Hide we simply include the same code but reverse the check perform to ensure it is only called if the Toolbar is not visible. We have included this code to the Workbook_Deactivate procedure so that it is called each time the workbook is not active or the workbook is closed.
If Application.CommandBars("Ribbon").Height < 60 Then
To help you get started here is a link to a Sample Workbook that includes the Macro discussed above.
|Download Sample Workbook|
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment