Run a Macro Automatically on Opening Excel Workbook

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.

• Show a welcome message to the user
• Run some starting scripts on opening the workbook
• Fill or populate the drop-down lists or other ActiveX Control while opening the workbook
• Activate a particular sheet while opening the workbook
• Show a user form while opening the workbook
• Clear the specific worksheets or ranges while opening the workbook
• Download and see it practically

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:

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