In today’s article, we are going to learn about how to run a set of instructions via a macro when a workbook is opened. Running a VBA code when a workbook is just opened can make us write a welcome message, display the total number of sheets on the worksheet or set a default sheet to be activated from a list of worksheets in a workbook.
Run a Macro when a Workbook is Opened
When you want to run a VBA code when excel starts, you can follow any of these two methods.
- Workbook_Open() – We can use a Sub Procedure named workbook_open in the This_Workbook Module to run the code when the workbook opens.
- Auto_Open() – We can use the Sub Procedure named auto_open in any module. The code will be executed when excel starts.
You can download the practice workbook from here.
Example 1 – Display a Welcome Message when the Workbook Opens
We would use the Workbook_Open method in this example. Now follow these steps.
- Open the Excel Workbook to which you wish to add a Welcome message. You can also take a new workbook in excel.
- Go to the Developer tab and click on the Visual Basic Button. This opens the VBE (Visual Basic Editor)
- Go to Project Explorer at the left and click on ThisWorkbook. This opens the Module for the current workbook.
- Now Insert a Private Sub Procedure named Workbook_Open.
- Use the following line of code in ThisWorkbook Module to print a welcome message.
Private Sub Workbook_Open()
MsgBox "Hey! Welcome to Excel Unlocked"
End Sub- Press Alt and F4 keys to close the VBA editor. Now save the excel workbook as Excel Macro Enabled Workbook.
- Now when anyone opens this workbook, a welcome message is displayed like this.
Example 2 – Set a Default Sheet when Workbook Opens
We can set a default sheet from all the worksheets to open first when the workbook is opened. We would use Auto_Open method in this example. Note that we can place Auto_Open Sub Procedure in any module of the Workbook VBA project.
- Let us say we have two worksheets in our workbook as follows. We want that every time this workbook opens, sheet2 gets activated automatically no matter where we left the work when we closed the workbook last time.
- Open the Visual Basic Editor and insert a Standard Module in Workbook’s VBA project from the Insert tab.
- Enter the following code in the inserted module.
Sub Auto_Open()
Sheet2.Activate
End Sub
- Close the VBE. Activate Sheet1 and then close the excel file after saving it as Excel Macro Enabled Workbook.
Now we left the workbook with Sheet1 opened, but when we open the workbook, the welcome message is displayed which we added in the first example and then the sheet2 is activated.
This brings us to an end.
Thank you for reading.