How to Hide Formula Bar Using VBA in Excel (with Easy Steps)

Hiding the Formula Bar allows us to increase the working space in Excel. More rows will be visible after hiding the Formula Bar. We can hide the Formula Bar in Excel by using the VBA Macro option. This article will discuss three simple yet efficient steps to hide Formula Bar using VBA in Excel. So, let’s start this article and explore these steps.

Table of Contents hide

Download Practice Workbook

Benefits of Hiding Formula Bar in Excel

Steps to Hide Formula Bar Using VBA in Excel

Step 01: Insert New Module

Step 02: Write and Save VBA Code

Step 03: Run Macro to Hide Formula Bar

How to Display Formula Bar Using VBA in Excel

Conclusion

Download Practice Workbook

Hiding Formula Bar Using VBA.xlsm

Benefits of Hiding Formula Bar in Excel

Hiding Formula Bar is a personal preference in most cases. Nevertheless, hiding the Formula Bar comes with some benefits. Some of these benefits are discussed here.

Hiding the Formula Bar provides a larger space in the worksheet.
A greater number of rows will be visible in the worksheet.
Some people prefer to write the formula in cells instead of the Formula Bar. Hiding the Formula Bar will simplify their worksheets visually.

Steps to Hide Formula Bar Using VBA in Excel

In this section of the article, we will learn the steps to hide Formula Bar using VBA in Excel. Let’s say we have the Laptop Prices in Micro Center Store as our dataset. Our goal is to hide the Formula Bar. Now, let’s follow the steps mentioned below to do this. One thing to keep in mind is that, if you hide Formula Bar, this will always apply to the whole workbook. That means the Formula Bar will remain hidden for the entire workbook.

Step 01: Insert New Module

Before writing VBA code, we need to create a new Module in the VBA editor. Now, let’s use the instructions outlined below to insert a new Module.

Firstly, go to the Developer tab from Ribbon.
After that, choose the Visual Basic option from the Code group.

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

Now, in the Microsoft Visual Basic for Applications window, go to the Insert tab.
Then, choose the Module option from the drop-down.

Step 02: Write and Save VBA Code

After you have created a new Module, it’s time to write our VBA code inside the Module.

So, write the following code in the newly created Module.

Sub hide_formula_bar()
Application.DisplayFormulaBar = False
End Sub

Code Breakdown

Firstly, we created a sub-procedure named hide_formula_bar.
Following that, we used the Application.DisplayFormulaBar property of Excel to and set its value to False.
Then, we ended the sub-procedure.

After writing the code, click on the Save option.

Step 03: Run Macro to Hide Formula Bar

In our final step, we will run the saved macro. So, let’s use the procedure discussed below to hide Formula Bar using VBA in Excel.

Firstly, use the keyboard shortcut ALT + F11 to return to the worksheet.
Following that, go to the Developer tab from Ribbon.
Subsequently, choose the Macro option from the Code group.

Note: You can also use the keyboard shortcut ALT + F8 to open the Macro dialogue box.

After that, in the Macro dialogue box, choose the hide_formula_bar option.
Finally, click on Run.

Consequently, the Formula Bar will be hidden from the worksheet, as demonstrated in the following image.

How to Display Formula Bar Using VBA in Excel

In the previous methods, we learned to hide the Formula Bar using VBA in Excel. We can also show the Formula Bar in Excel using VBA. Let’s follow the steps mentioned below to do this.

Steps:

Firstly, use the instructions outlined in Step 01 of the first method to create a new Module.
Following that, write the following code.

Sub show_formula_bar()
Application.DisplayFormulaBar = True
End Sub

Code Breakdown

Firstly, we created a sub procedure named show_formula_bar.
After that, we used the Application.DisplayFormulaBar property of Excel to and set its value to True.
Finally, we ended the sub procedure.

After writing the code, click on the Save icon.

Then, use the keyboard shortcut ALT + F11 to return to the worksheet.
Following that, use the keyboard shortcut ALT + F8 to open the Macro dialogue box.
After that, in the Macro dialogue box, choose the show_formula_bar option.
Finally, click on Run.

Consequently, the Formula Bar will reappear on your worksheet, as demonstrated in the image below.

Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to hide Formula Bar using VBA in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.