How to Use VBA to Hide the Formula Bar and Collapse the Ribbon in Excel
Question details
The user wants a VBA macro to hide the formula bar and minimize the ribbon automatically when a workbook opens or a macro runs.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is setting up a workbook so the Excel interface is simplified automatically instead of manually hiding the formula bar or collapsing the ribbon each time.
- Observed behavior
- The desired state is for the formula bar to be hidden by VBA and the ribbon to be minimized when the workbook opens or the macro runs.
Open Excel and press Alt+F11 to access the VBA editor. Make sure the workbook is saved as a macro-enabled file (.xlsm) so the VBA code can run.
Run a Workbook_Open Macro to Hide the Formula Bar and Minimize the Ribbon Automatically
Use this when you want Excel to apply the simplified interface every time the workbook opens.
The code is placed in the ThisWorkbook module, so it runs automatically when the workbook opens. Application.DisplayFormulaBar is an application-level setting, so it affects all open workbooks in that Excel instance; the MinimizeRibbon command applies to the current workbook window.
In Excel, press Alt+F11 to open the VBA editor.
In the Project Explorer pane, find the current workbook, right-click ThisWorkbook, and choose View Code.
Paste the following code into the module: Private Sub Workbook_Open() Application.DisplayFormulaBar = False CommandBars.ExecuteMso "MinimizeRibbon" End Sub
Click File > Save As, set the file type to Excel Macro-Enabled Workbook (*.xlsm), and save.
Close the workbook and open it again with macros enabled to run Workbook_Open.
Run a Macro Manually to Hide the Formula Bar and Minimize the Ribbon
Use this when you want to trigger the change on demand instead of automatically at workbook open.
Use WPS Office as a Free, Lightweight Alternative for Excel Workbooks
If the Excel-specific VBA object model or CommandBars method is unavailable in your environment, WPS Office is a free, lightweight Microsoft Office alternative that opens Excel workbooks, keeps a familiar spreadsheet interface, and supports smooth migration for everyday tasks.
- 1. Download WPS Office: Go to https://www.wps.com and download WPS Office for your device, then install it.
- 2. Open your Excel workbook: Launch WPS Office, click File > Open, and choose the .xlsm or .xlsx workbook you need to review.
- 3. Check the macro and display settings: Use the View tab to adjust the formula bar and ribbon display, or press Alt+F11 to open the macro editor and check the available macro features.
Frequently Asked Questions
Does hiding the formula bar with VBA affect other workbooks?
Yes. Application.DisplayFormulaBar is application-wide, so it affects all open workbooks in the same Excel instance. Use Application.DisplayFormulaBar = True to restore it.
Can I run this macro only for one workbook?
The Workbook_Open event runs only for that workbook, but the formula bar change is application-wide. The minimized ribbon state is specific to the workbook window.
Why is my macro not running when I open the workbook?
Confirm that the file is saved as a macro-enabled workbook (.xlsm), macros are enabled in the Trust Center, and the code is placed in ThisWorkbook rather than a standard module.
How do I restore the formula bar and ribbon later?
Run a macro with Application.DisplayFormulaBar = True and use the ribbon expand control or a VBA command to restore the ribbon.
