Home  ›  How to Make Excel Macro Run Continuously

How to Make Excel Macro Run Continuously

Written By Thursday, October 27, 2022 Add Comment Edit
Skip to content

Run a Macro Automatically on Opening Excel Workbook

Home » Excel VBA » Run a Macro Automatically on Opening Excel Workbook
  • run a macro automatically

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:

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

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

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.

Excel VBA Project Management Templates
All-in-One Pack
120+ Project Management
Premium Templates
Essential Pack
50+ Project Management
Premium Templates
50+ Excel
Project Management
Templates Pack
50+ PowerPoint
Project Management
Templates Pack
25+ MS Word
Project Management
Templates Pack
Ultimate Project Management Template
Ultimate Resource Management Template
Project Portfolio Management Templates

Share This Story, Choose Your Platform!

15 Comments

  1. 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

  2. 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!

  3. 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

  4. Excellent help, thanks. Gabor

  5. Hi,

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

  6. 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

  7. 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 ??

  8. 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…

  9. 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.

  10. 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.

  11. 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.

  12. 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

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

  14. Paste the below code in the workbook code module:


    Private Sub Workbook_Open()
    Call Main
    End Sub

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

Effectively Manage Your
Projects and  Resources

With Our Professional and Premium Project Management Templates!

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We're a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We're Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Download Free Excel 2007, 2010, 2013 Add-in for Creating Innovative Dashboards, Tools for Data Mining, Analysis, Visualization. Learn VBA for MS Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Page load link
Go to Top

mireleseastche.blogspot.com

Source: https://analysistabs.com/excel-vba/run-macro-automatically-opening-workbook/

0 Response to "How to Make Excel Macro Run Continuously"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel