Description:

Sometimes you may need to run a macro automatically on opening excel workbook. Following are the few cases where you are required to run a macro while opening your excel workbook.

Run a Macro Automatically on Opening Excel Workbook – Solution(s):

We can use Workbook_Open() method or Auto_Open() method to achieve this.

Run a Macro Automatically – Example Cases:

Following are the list of situations where we need to run a macro automatically on opening an excel workbook.

Showing a welcome message to the user

When you open a workbook, you may want to pass some instructions to the user. Or you can show a welcome message with specific text or user name. The following code will show you how to Run a Macro Automatically.

Code:
Private Sub Workbook_Open()
Msgbox "Welcome to ANALYSIS TABS"
End Sub
Output:

run a macro automatically

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Double click on ThisWorkbook from Project Explorer
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Open the workbook to test it, it will Run a Macro Automatically. You should see a message box as shown above
Using Auto open method to run a macro automatically:

You can INSERT a new module and place the following code in the newly created module

Code:
Sub Auto_Open()
Msgbox "Welcome to ANALYSIS TABS"
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a New Module from Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Open the workbook to test it, it will Run a Macro Automatically. You should see a message box as shown above
Running some starting scripts on opening the workbook

The following example runs a script to count the number of worksheets in workbook and list out them in the sheet1.

Code:
Sub Auto_Open()
Dim sh
Dim iCntr
iCntr = 1
For Each sh In ThisWorkbook.Sheets
Sheet1.Cells(iCntr, 1) = sh.Name
iCntr = iCntr + 1
Next
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a New Module from Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Open the workbook to test it, it will Run a Macro Automatically. You should see the list of sheet names in the Sheet1
You may use following code to Populate a Combo Box or a List Box in worksheet

The following example shows how to populate regions(East,West,North,South) in a ComboBox and a List Box

Code:
Sub Auto_Open()
Sheet1.ComboBox1.AddItem "East"
Sheet1.ComboBox1.AddItem "West"
Sheet1.ComboBox1.AddItem "North"
Sheet1.ComboBox1.AddItem "South"
With Sheets("Sheet1").ListBox1
.AddItem "East"
.AddItem "West"
.AddItem "North"
.AddItem "South"
End With
End Sub
Instructions:
  1. Open an excel workbook
  2. Insert a Combobox (activex control from developer ribbon) in the Sheet1. And Name the Combo Box (right click on it and change the name in the properties) as ComboBox1
  3. Insert a Listbox (activex control from developer ribbon) in the Sheet1. And Name the List Box (right click on it and change the name in the properties) as Listbox1
  4. Press Alt+F11 to open VBA Editor
  5. Double click on ThisWorkbook from Project Explorer
  6. Copy the above code and Paste in the code window
  7. Save the file as macro enabled workbook
  8. Open the workbook to test it, it will Run a Macro Automatically. You should see the Combo Box and List Box in the Sheet1 are filled with items
You may use following code to Activate a Sheet or Show an UserForm

The following example shows to activate a sheet (named “Home”) and show an userform (UserForm1). This code will activate the “Home” worksheet and then display the UserForm1

Code:
Sub Auto_Open()
'Activate a Sheet
Sheets("Home").Activate
'Show an UserForm
UserForm1.Show
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a userform from Insert menu (UserForm1)
  4. Double click on ThisWorkbook from Project Explorer
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Open the workbook to test it, it will Run a Macro Automatically. You should see the Userform which you have created
You may want to clear the specific worksheets or ranges while opening the workbook

The following example clears the all worksheets in the workbook on workbook open.

Code:
Sub Auto_Open()
Dim sh
For Each sh In ThisWorkbook.Sheets
sh .Cells.Clear
Next
End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some sample data in each workbook
  3. Press Alt+F11 to open VBA Editor
  4. Double click on ThisWorkbook from Project Explorer
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Open the workbook to test it, it will Run a Macro Automatically. You should see all the worksheets are cleared
You can download the example file and see how it’s working.

Please note: We may comment have commented some code as we can write only one auto_open() or Workbook_open() procedure.

ANALYSISTABS – Run a Macro Automatically

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!