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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of methods to hide Formula Bar using VBA in Excel


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.

Read More: How to Hide Cell Contents in Excel Formula Bar


Hide Formula Bar Using VBA in Excel: With Easy Steps

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 the 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 the Formula Bar, this will always apply to the whole workbook. That means the Formula Bar will remain hidden for the entire workbook.

Sample dataset


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.

Using Developer tab to hide Formula Bar using VBA in Excel 

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

Microsoft Visual Basic for Applications window 

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

Inserting new Module


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

Writing VBA code in new Module to hide Formula Bar using VBA in Excel 

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.

Saving the VBA code 


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.

Using the Developer tab to open the Macro dialogue box

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

Running macro to hide Formula Bar using VBA in Excel 

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

Output obtained after hiding Formula Bar in Excel 


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

Writing VBA code to display Formula Bar using VBA in Excel

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.

Saving the VBA code

  • 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.

Running macro to show Formula Bar in Excel

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

Output achieved after using the macro

Read More: [Fixed!] Formula Bar Is Not Showing in Excel


Download Practice Workbook


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.


Related Articles


<< Go Back to Excel Formula Bar | Excel Parts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo