Excel VBA Open Workbook Event (All in One)

Are you tired of performing repetitive tasks every time you open an Excel workbook? Do you want to automate your workflow and save time? If your answer is yes, then you have come to the right place. In this article, we will discuss the Excel VBA Open Workbook Event and how you can use it to automate various tasks when you open an Excel workbook.

Here, we have provided six examples of how you can use the Open Workbook Event in VBA to automate tasks. These examples range from creating a new worksheet with a predefined name every time the workbook opens to activating a specific worksheet and entering a value when the workbook opens. By following these examples, you can save time and improve your productivity. Additionally, we will provide a step-by-step guide on how to use the Open Workbook Event, so even if you are new to VBA programming, you can easily follow along. So, if you want to learn how to automate tasks in Excel using VBA, keep reading!

Download Practice Workbook

Download the Excel workbook file from the link below to practice.

How to Launch Visual Basic Editor in Excel

To Launch Visual Basic Editor, we need to click on Visual Basic under the Developer tab.

Then, double-click on “This Workbook”. Finally, inside the workbook event, you can write suitable codes to get your desired output. You have to repeat this process for adding each of the new Macros. (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon excel)

What Are VBA Workbook Events in Excel?

The VBA Open_Workbook event is a type of workbook event in Excel that is triggered whenever a workbook is opened. In VBA, events are actions that occur when certain conditions are met. There are several types of events in Excel VBA:

• Application events are triggered whenever an event occurs within the Excel application, such as when a workbook is opened or closed, or when a new sheet is added.
• Workbook events are triggered by actions within a specific workbook, such as when the workbook is opened or closed, when a sheet is activated, or when a cell value is changed.
• Worksheet events are triggered by actions within a specific worksheet, such as when a cell value is changed, when a new sheet is added, or when a chart is updated.
• UserForm events are triggered by actions within a specific UserForm, such as when the UserForm is opened or closed, when a button is clicked, or when a textbox value is changed.
• Chart events are triggered by actions within a specific chart, such as when a chart is activated, when a chart is resized, or when a chart element is clicked.

Some commonly used VBA workbook events include:

• Open event: This event is triggered when you open a workbook.
• BeforeClose event: This event is triggered just before a you close a workbook. It can be used to perform certain actions like saving changes to the workbook.
• BeforeSave event: This event is triggered just before a workbook is saved. It can be used to perform certain actions like validating data or performing calculations.
• SheetActivate event: This event is triggered when a worksheet is activated.
• SheetChange event: This event is triggered when the contents of a cell in a worksheet are changed.
• SheetDeactivate event: This event is triggered when a worksheet is deactivated.
• NewSheet event: This event is triggered when a new worksheet is added to a workbook.
• WindowActivate event: This event is triggered when you activate a workbook window.
• WindowResize event: This event is triggered when a workbook window is resized.
• WindowScroll event: This event is triggered when the user scrolls through a workbook window.

Overview of VBA Open_Workbook Event in Excel VBA

The Workbook_Open event is one of the most commonly used events in VBA, and it is triggered when an Excel workbook is opened. This event can be used to run VBA code automatically when the workbook is opened, and it can be used to perform a variety of tasks, such as setting default values, formatting cells, or displaying messages.

Overall, the Workbook_Open event is a powerful tool that can be used to customize the behavior of Excel workbooks and to automate repetitive tasks. By using this event effectively, you can improve the usability and functionality of your workbooks, and you can save time and increase productivity. Below is an example:

Code Syntax:

Private Sub Workbook_Open()
MsgBox "Welcome to my Excel file!"
End Sub

This code will display a message box with the text “Welcome to my Excel file!” every time the workbook is opened.

Difference Between Workbook_Open Event and Auto_Open Event in Excel VBA

Workbook_Open and Auto_Open are two similar events in VBA that are triggered when an Excel workbook is opened, but they have some differences:

• Workbook_Open is an event that can be attached to a specific workbook so that it is triggered only when that particular workbook is opened. On the other hand, Auto_Open is a macro that is always executed when the workbook containing it is opened.
• Workbook_Open event is placed inside “This Workbook”. But the Auto_Open event is placed inside any of the modules.
• Auto_Open is an older event that was used in earlier versions of Excel, while Workbook_Open is a newer event that was introduced in Excel 2000.
• Auto_Open can be disabled by users who have disabled macros, while Workbook_Open cannot be disabled in this way.

In general, it is recommended to use Workbook_Open instead of Auto_Open, as it provides more flexibility and is less likely to be disabled by users who have disabled macros.

Excel VBA Open Workbook Event: 6 Suitable Examples

In this section, We have taken a Dataset named “Employee Information Table” to carry out the demonstration of the following examples.

1. Create New Worksheet with Predefined Name Everytime Excel Workbook Opens

In this example, this VBA code adds a new worksheet named “Address” to the workbook every time the workbook is opened. If a worksheet named “Address” already exists, it displays a message box with an error message. It also hides gridlines in the new worksheet.

To Test this code, double-click on “This Workbook” Inside the Visual Basic Window. And type the following Code:

Code:

Private Sub Workbook_Open()
On Error GoTo Errorhandler
Dim ws As Worksheet
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Address"
ActiveWindow.DisplayGridlines = False
MsgBox ("A New Sheet named 'Address' is Created.")
Exit Sub
Errorhandler:
ActiveWindow.DisplayGridlines = False
MsgBox ("A sheet named 'Address' already exists.")
End Sub

Private Sub Workbook_Open()

On Error GoTo Errorhandler

Dim ws As Worksheet

Sheets.Add After:=Sheets(Sheets.Count)

Name = "Address"

DisplayGridlines = False

MsgBox ("A New Sheet named 'Address' is Created.")

Exit Sub

Errorhandler

DisplayGridlines = False

MsgBox ("A sheet named 'Address' already exists.")

End Sub

After that, Save the workbook, close it, and then reopen it.

You will see that a new worksheet is opened with the name “Address” inside the workbook.

2. Set Default ActiveSheet Everytime Workbook Opens

In this example, the VBA code activates the worksheet named “Contact Number” in the workbook every time the workbook is opened.

To Test this code, double-click on “This Workbook” Inside the Visual Basic Window. And type the following Code:

Code:

Private Sub Workbook_Open()
ThisWorkbook.Sheets("Contact Number").Activate
MsgBox ("This is your default Active Sheet.")
End Sub

Private Sub Workbook_Open()

Sheets("Contact Number")

.Activate

MsgBox ("This is your default Active Sheet.")

End Sub

After that, Save the workbook, close it, and then reopen it.

You will see that the worksheet named “Contact Number” is always the default activated sheet every time the workbook opens, even though it is not the first worksheet inside the workbook.

3. Load Predesigned UserForm Everytime Workbook Opens

In this example, a predesigned Userform is always displayed instantly after the workbook opens.

To Test this code, insert a Userform inside the Visual Basic Window and design the Userform according to your need.

Then type the following code inside “This Workbook”:

Code Syntax:

Private Sub Workbook_Open()
EmployeeForm.Show
End Sub

Private Sub Workbook_Open()

Show

End Sub

After that, Save the workbook, close it, and then reopen it.

You will see that the Userform you have designed, pops out instantly after you open the workbook all the time.

4. Displaying Desired MsgBox Every Time Workbook Opens Based on Particular Day

In this example, the VBA code displays a message box with a different message depending on whether the current day is Sunday or not. If the current day is Sunday, the message “Today is Sunday! Enjoy your Weekend!” is displayed. Otherwise, if the current day is a weekday, the message “Welcome! Let’s Make a Difference.” is displayed.

To Test this code, double-click on “This Workbook” Inside the Visual Basic Window. And type the following Code:

Code Syntax:

Private Sub Workbook_Open()
If Weekday(Now) = 1 Then
Msg = "Today is Sunday! Enjoy your Weekend!"
MsgBox Msg, vbOKOnly
Else
Msg = "Welcome! Let's Make a Difference."
MsgBox Msg, vbOKOnly
End If
End Sub

Private Sub Workbook_Open()

If Weekday(Now) = 1 Then

Msg = "Today is Sunday! Enjoy your Weekend!"

MsgBox Msg, vbOKOnly

Else

Msg = "Welcome! Let's Make a Difference."

MsgBox Msg, vbOKOnly

End If

End Sub

After that, Save the workbook, close it, and then reopen it.

If today is Sunday, you will get the following output.

Else, for any other day, you will get the following output instead.

Press OK to terminate the Macro.

5. Prompting User to Set Worksheet Name When Workbook Opens

In this example, the VBA code prompts the user to enter a name for a new worksheet when the workbook is opened. If the user enters a name, it creates a new worksheet with the specified name and hides the gridlines. If the user does not enter a name, it displays a message box prompting the user to enter a name. Also, If the specified name already exists, it displays an error message.

To Test this code, double-click on “This Workbook” Inside the Visual Basic Window. And type the following Code:

Code Syntax:

Private Sub Workbook_Open()
Dim wsName As String
wsName = InputBox("Enter a name for the new worksheet:")
If wsName = "" Then
MsgBox ("Please Enter a Worksheet Name.")
Else
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
On Error GoTo ErrorHandler
ActiveSheet.Name = wsName
ActiveWindow.DisplayGridlines = False
Exit Sub
ErrorHandler:
ActiveWindow.DisplayGridlines = False
MsgBox ("There is already a worksheet with the same name.")
End If
End Sub

Private Sub Workbook_Open()

Dim wsName As String

wsName = InputBox("Enter a name for the new worksheet:")

If wsName = "" Then

MsgBox ("Please Enter a Worksheet Name.")

Else

Sheets.Add After:=Sheets(Sheets.Count)

On Error GoTo ErrorHandler

Name = wsName

DisplayGridlines = False

Exit Sub

ErrorHandler

DisplayGridlines = False

MsgBox ("There is already a worksheet with the same name.")

End If

End Sub

After that, Save the workbook, close it, and then reopen it.

You can enter your desired name and press OK. A new worksheet with the entered name will be opened. If you keep the inputbox empty or type a name that already exists for an existing worksheet, different error messages will be displayed.

6. Enter a Value Inside a Specific Worksheet When Workbook Opens

In this example, the code opens the Excel workbook and maximizes the window, activates the “DataEntry” worksheet, selects a specific cell, and sets its value to the current date. If the current date is already inside the desired cell, it does not reenter the value and also does not show any error message.

To Test this code, double-click on “This Workbook” Inside the Visual Basic Window. And type the following Code:

Code Syntax:

Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMaximized
Worksheets("DataEntry").Activate
On Error GoTo Errorhandler
Range("B1").End(xlDown).Offset(-2, 0).Select
ActiveCell.Value = Date
Errorhandler:
Application.DisplayAlerts = False
End Sub

Private Sub Workbook_Open()

WindowState = xlMaximized

Worksheets("DataEntry").Activate

On Error GoTo Errorhandler

Range("B1").End(xlDown).Offset(-2, 0).Select

Value = Date

Errorhandler

DisplayAlerts = False

End Sub

After that, Save the workbook, close it, and then reopen it.

 The Current Date Will be Visible inside the DataEntry Worksheet above the table.

What to Do If VBA Open Workbook Event is Not Working in Excel?

If the VBA Open Workbook event is not working in Excel, there are several things you can try such as:

• First, make sure that you have saved the workbook as a macro-enabled file with the extension .xlsm. If the file is saved with a different extension, the VBA code will not execute.
• Another thing to check is that the workbook actually contains VBA code. Open the VBA editor (Alt + F11) and check that there is code in the Workbook_Open If there is no code, add some and save the workbook.
• If the code is present, but still not executing, check if the workbook is set to automatically calculate formulas. If not, go to the Formulas tab, select Calculation Options, and then Automatic.

Lastly, it is possible that the security settings on your computer are preventing the VBA code from running. Go to the File tab, select Options, and then Trust Center. Click on Trust Center Settings and ensure that you have enabled macros.

• By following these steps, you can ensure that the VBA Open Workbook event works as expected in Excel.

How to Open Workbook from Path with Excel VBA

Suppose, you are working inside a workbook. But you need to open another workbook for some reason. You can do that by just running a simple macro. You don’t need to close or minimize the current workbook to do that. Here is how:

• Open a new or existing Excel workbook.
• Press the Alt + F11 keys to open the Visual Basic Editor (VBE).
• In the VBE, insert a new module by clicking on “Insert” > “Module” in the menu bar or by pressing Alt + I + M.
• In the new module, enter the following code:

Code Syntax:

Sub OpenWorkbookFromPath()
Dim wb As Workbook
FilePath = "C:\Users\User\Desktop\SOFTEKO\Article 13\.xlsm files\6. Activate Specific Worksheet and Enter a Value When Workbook Opens (Ex6).xlsm"
Set wb = Workbooks.Open(FilePath)
MsgBox "The name of the workbook is: '" & wb.Name & "'"
End Sub

Things to Remember

• Save the workbook in a macro-enabled format (.xlsm).
• Remember that the Open_Workbook event only runs when you open the workbook, not when it is already open.
• Avoid using the Open_Workbook event for tasks that are not related to opening the workbook, as it can lead to confusion and unnecessary code execution.

Conclusion

Hope this article has helped you to learn how to use the Open Workbook event in Excel VBA. What is the Function that you have automated using the Open_Workbook event? Please, do share with us. Feel free to mention any queries in the comment section below. Also, you can visit Exceldemy for gaining firm knowledge regarding the various uses of MS Excel.