Run a Macro Automatically on Opening Excel Workbook

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 mongopono.ru"
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 mongopono.ru"
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.

mongopono.ru – Run a Macro Automatically

LIMITED TIME OFFER
By |January 8th, 2013|Excel VBA|10 Comments

About the Author:

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.

10 Comments

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

    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 - Reply

      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 - Reply

    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 - Reply

    Excellent help, thanks. Gabor

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

    Hi,

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

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

    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 - Reply

    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 - Reply

    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 - Reply

    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 - Reply

    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.

Leave A Comment


Related pages


excel lookup formulahow to consolidate excel sheetsexcel vba class module exampleexcel remove all blank rowsinterview questions in testing for freshersproject management timeline exceltrim formula excelshort cut keys in ms excelvba carriage returnexcel worksheet copyexcel formula templatesbasic vlookup exampleappend text filesvba file copyvba open csvexcel vba select columnsprogramming vbasql update vbaremove duplicates macrovba iconms powerpoint questions and answerscounting duplicates in excellearning pivot tables in excel 2007what is the iferror function in excelexcel vba usernamehow to unhide all columns in excelproject dashboard excel template freeiserror excelhow to add a blank row in exceleasy excel vbaproject management powerpoint presentation templateworkbook activateunload userformcreate userform in excel 2007sql coding for beginnersduplicate rows excelselect case vba excelexcel vba sqlchange row to column in excelexcel vba chart objectlcase vbasql queries interview questions and answers pdf free downloadhow to delete blank row in excelcopy vba codehow to remove duplicate data in excel 2010excel vba hide sheetexcel function syntaxvlookup applicationhow to edit the protected excel sheetexcel trim functionhow to delete blank cells in excelexcel vba sort arrayexcel how to delete empty rowsexcel vba dynamic rangewhat is concatenate in excelsendmail syntaxexcel vba programming ebookexcel color templateexcel concatenate formulavba switchhow to remove the blank rows in exceladd chart title excelexcel tutorial beginnersactivex exampleexcel macro chartexcel all formulas with example 2007merging excelenable macros vbafree pnroms access visual basic commandsmultiple if statements excelmove worksheet to another workbook