CTRL+F1 to Hide the Excel Toolbar using VBA

Feb 15th 2013, Category: Macros, Tags: , ,

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.

The standard Toolbar view looks like



Then when the Toolbar is hidden we want the view to look like this


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.

Private Sub Workbook_Activate()
    If Application.CommandBars("Ribbon").Height > 60 Then
        Application.SendKeys ("^{F1}")
    End If
End Sub

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.

Private Sub Workbook_Deactivate()
    If Application.CommandBars("Ribbon").Height < 60 Then
        Application.SendKeys ("^{F1}")
    End If
End Sub

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

Related posts:

  1. Disable Alerts and Warning Messages in Excel
  2. Copy Email Items to Excel Workbook
  3. Extract Workbook Name from Full Filename
  4. Open a Workbook
  5. Shortcut to Last Worksheet used

Discussion

No responses to "CTRL+F1 to Hide the Excel Toolbar using VBA"

There are no comments yet, add one below.

Leave a Comment

Wrap code blocks with <pre lang="VB" > code goes here </pre> to appear with VBA formatting.



SUBSCRIBE WITHOUT COMENTING