Run VBA Code Automatically on Workbook Open

In Excel automations, running VBA code upon workbook opening can streamline processes and boost efficiency. From this post you will explore the possibilities of this advanced feature, allowing you to tailor Excel to your unique needs and maximize productivity in your daily workflow. Then this article will guide you through the steps to run a VBA code when opening an Excel file using an example.

First of all let’s explore the possibility of this advanced feature.

• Data Refresh: If your workbook relies on external data sources, running VBA code on open can trigger an automatic refresh, ensuring that your data is always up-to-date.
• Security Measures: You may want to use password prompts when opening the workbook, ensuring that only authorized users can use the file. For this you can use this technique to show a login form when opening the workbook. Check this post to learn how to show a userform automatically when opening an Excel file.
  Show Userform Automatically When Opening Excel File
• User Interface Customization: You can use this technique to VBA to customize the workbook's interface, displaying specific sheets upon opening. For example, your workbook may have several worksheets and you may want to show only a particular worksheet on the workbook open. This is the usage I showed in the example below.
• Initialization Tasks: Running VBA code on open is handy for initializing variables, setting default values, or configuring the environment to default state.
• Version Control: You can use this technique to VBA to log information about when the workbook was last opened, helping with tracking changes.
• Connected Workbooks: In scenarios where multiple workbooks are interlinked, running VBA code on open can establish connections.
• Alerts and Notifications: Displaying alerts or notifications when opening the Excel VBA application. In some Excel VBA applications you might need to inform users about important updates or pending tasks whe he/she opens the application.
• Automated Backups: This technique can be used to create an automatic backup system for your Excel VBA application. For an example you can tell the VBA to create a backup every time when a user opens the application. Or you can limit it to create only one backup per day if the user opens the application multiple times within one day.

So now you explored various possibilities of this feature. Next let’s learn how to write a code to do this. I will explain this using an example.

This example belongs to user interface customization. Assume your workbook has 3 worksheets called “Input”, “Report”, “Settings”. Suppose you want to only show the “Input” worksheet when the user opens the workbook hiding the other sheets. Users might be able to see other sheets using buttons while using the application.

Now first of all we need to develop a simple macro to show only the “Input” tab hiding other sheets.

Sub ShowOnlyInput()

   Worksheets("Input").Visible = True
   Worksheets("Report").Visible = xlVeryHidden
   Worksheets("Settings").Visible = xlVeryHidden

End Sub

Note that there are different ways to hide worksheets using VBA. Some methods don't allow users to manually unhide sheets when hidden with VBA. Check this post if you like to learn more about hiding and unhiding worksheets in Excel.

Hide And Unhide Worksheets Using VBA

We can put above simple code inside a module of the VBA project. Don’t know how to insert a module to a VBA project? Then check this post.

How to Insert Modules in Excel VBA Projects

Now we need a way to run this subroutine when the user opens the workbook. To do that we can use the workbook open event. Events are occurrences or triggers that happen within the Excel application, and VBA allows you to write code that responds to these events. So if you write a code inside the Workbook.Open event then it will be executed when the user opens the workbook.

Follow these easy steps to add code to the workbook open event.

Go to the VBA editor and double click on the ThisWorkbook Module.

Now select “Workbook” from the first dropdown.

When you select the “Workbook” from the dropdown, the second dropdown will be automatically changed to “Open” and the following code will be added to the ThisWorkbook module.

Also if you look at the second dropdown, you will notice that there are lots of other events available related to the Workbook object.

Now we can run the previous subroutine we wrote by calling it from inside the Open event of the Workbook.

Private Sub Workbook_Open()

   Call ShowOnlyInput

End Sub

Also instead of putting the code in a separate subroutine, you can put the code directly inside the Workbook_Open event as follows.

Private Sub Workbook_Open()

   Worksheets("Input").Visible = True
   Worksheets("Report").Visible = xlVeryHidden
   Worksheets("Settings").Visible = xlVeryHidden

End Sub

But it is always a good practice to divide the code into meaningful sections. Because it enhances readability, maintainability, and overall code quality. When code is divided into sections, each part focuses on a specific task or functionality. This makes it easier for developers (including the original coder or others) to understand the purpose and flow of each section.