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

Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: January 8th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

15 Comments

  1. Haobo December 30, 2013 at 4:32 AM

    Thank you for the examples. Just wonder that after Dim syntax, should you declare the data type?
    Sub Auto_Open()

    Dim sh as worksheet

    For Each sh In ThisWorkbook.Sheets
    sh .Cells.Clear
    Next

    End Sub

    • PNRao December 30, 2013 at 10:08 PM

      Hi Haobo,

      Thanks for your comments.
      Yes, it is good practice to declare the data type of the variable always.However, Excel can automatically define internally based on the data assigned to that particular variable in the program.

      Thanks-PNRao!

  2. Fauzan May 15, 2014 at 12:11 PM

    Actually I want to create a macro but didnt have the information of VBA to create that.

    Could some one plz help me!!!!!!!!!!!

    I want that if any mail comes to me with a subject of completed so i want a reminder should remind me within 5 mins and it should go on for a day as I am continuosly receiving this type of emails

  3. Gabor Horvath October 7, 2014 at 6:49 PM

    Excellent help, thanks. Gabor

  4. lokesh reddy July 22, 2015 at 7:40 AM

    Hi,

    I think we need explanation about coding in depth. example :dim sh, dim icntr

  5. Prabhu October 5, 2015 at 10:52 AM

    Hi I have a macro file,it wil automatically run wen the excel open up but sometime the code is not working the macro is not automatically run,plsss somebody help

    anyone know what is the issue in this

  6. RAHUL April 18, 2016 at 5:26 PM

    Hello, I want that with out opening the workbook the macros will run by taking the system date and it will send the mail .
    Can anybody help me on this ??

  7. Venkat May 24, 2016 at 7:46 PM

    Dear Mr.P.N Rao ..

    Hello !
    1. Can a macro be enabled automatically with a code inside sub auto_open() to avoid the prompt by macro security level settings prompting the user to press enable macro to run the auto_open() macro ??..Thanks in advance…

  8. gurunath March 10, 2017 at 10:20 AM

    Hi,
    I want to create one macro, in one Excel file more than 30 sheets are there. when ever i opens excel file it should open home sheet and also while opening o dont want to show all other sheets list.

    any one help me on this.

  9. Phani Ch March 31, 2017 at 9:35 AM

    I have written a macro for my department. Now my requirement is when we generate the excel from the application, Macro should automatically run by cross checking the Worksheet Name.
    Eg:- “DailyDetails”. If the generated sheet has a name like “DailyDetails_31/3/2017” it should run automatically without any shortcuts and if the generated sheet name is not as per criteria it should not run the macro.

  10. Alaine September 7, 2018 at 3:10 PM

    Hi, i would want to ask, how can i automatically run macros on a hyperlink form upon opening an excel file?

    I’m thinking of setting a specific date in outlook where it would open an excel file then run the macro automatically, but i don’t know how to call a specific hyperlink macro.

    hope you can help me.

  11. Aniket Shinde May 13, 2019 at 1:26 PM

    I Have a Data of Total Employees, Need to create VBA code on Employee Code. When I click on “Generate” need to show Total details of That seprate Employee.

    Please help with Code

  12. algae June 5, 2019 at 12:24 PM

    My workbook has a macro name “Main”. How do I run it whenever the workbook is opened?

    • PNRao July 4, 2019 at 5:56 PM

      Paste the below code in the workbook code module:


      Private Sub Workbook_Open()
      Call Main
      End Sub

  13. Devmode May 11, 2020 at 11:35 PM

    How would I make it auto-run a macro ONLY when I open documents from a particular folder on my desktop?

Leave A Comment