Apr 16 2009

Run a Macro When Opening a Workbook

There may be a time when you want a macro to run automatically every time you open a workbook.  Excel has a built in way to help with this – the Auto_Open subroutine (Workbook_Open achieves a similar result).  Each time Excel opens, Auto_Open() will run only once when the workbook is open.  Let’s go ahead and take a look at an example.

[ad#in-post-ad]

Auto_Open Example

Sub Auto_Open()
Msgbox(“Testing Auto_Open”)
End Sub

The is obviously a very simple example, but if you place this code in your module then save and close your workbook, the feature can then be tested.  Testing is as easy as opening your newly saved workbook.  When the program is open you will see the text you entered: Testing Auto_Open.

Uses for Auto_Open
The number of ways you can use this feature is endless, but here are just a few ways I have used this feature recently:

  • Automatically refresh database queries in your workbook upon opening
  • Prompt the user for parameters to update your spreadsheet with.
  • Automatically update the report with new data to present to the user.

In Summary
Like I said before, Workbook_Open is a similar macro that can be used almost interchangeably with Auto_Open.  Again, I believe the easiest way to become comfortable with writing macros is to record a macro using Excel’s built in recorder and copy that code to where you would like it used (perhaps the Auto_Open feature in the case).

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS:

7 Comments on this post

Trackbacks

  1. Affordable Housing said:

    I would like to place a button in an Excel spreadsheet that will run a macro. I already know how to create a macro, and I intend to use the same workbook over & over each month. I also need to know more about storing macro’s in the worksheet. This macro will go to a specific directory, open a specific file, manipulate, copy & paste that file into this spreadsheet, update the pivot table and populate other sheets throughout the entire workbook. I would like to place buttons on all the worksheets that contain data, around 12 sheets. These sheets then populate 3 reports that are due the first week of the month.

    May 14th, 2009 at 11:47 am
  2. Billy Gee said:

    Very clear and concise explanation. I imagine you could run other procedures where you put the msgbox in your example, if you had to perform several tasks upon opening.

    June 11th, 2009 at 3:16 pm
  3. john said:

    Yes, you can put any actions you want to perform inside the auto-open macro to get your program started.

    June 11th, 2009 at 7:03 pm
  4. Rahil said:

    Hi All & EH
    I have a table with colums Serial number, other colum feilds and total, for which I usually sort the rows according to their ascending order or Descending order, Doing which it changes the serial number of the rows in first column,

    To avoid this serial number issue I have written a macro using ‘For’ loop, and had assign short cut key of combination with Ctrl+Key
    and it work fine for sheet1.

    Sub Mymacro()
    For x = 1 To 15
    Cells((x + 1), 1) = x
    Next x
    End Sub

    But the issue is; when I am in sheet2 and press the shortcut key,
    The macro runs in the current sheet2 and prints the same output in sheet2 also,

    So therefore, can anybody help me out how to restrict the macro for a perticular sheet1 only, though I run it from anysheet, that has to work for perticular sheet only.

    & I am really great thankful to all the Commentators who gives real informative information …

    March 30th, 2010 at 12:29 am
  5. john said:

    If you just put the following line before your for loop you should be fine:

    sheets(“sheet1″).select

    If you want to return to the original sheet you were on when you ran the macro, that will require additional code.

    Good luck.

    March 30th, 2010 at 7:00 am
  6. JohnB said:

    How can I create a spreadsheet so that each time I open a new SHEET, a cell gets populated with an incremented number? i.e. if cell A1 in sheet 4 contains the number 5, if i create a new sheet, cell A1 in sheet 5 will contain the number 6?

    July 7th, 2011 at 11:56 am
  7. chrish said:

    When I tried this and reopened the wkbk, I get the message:

    Ambigious name detected: Auto_Open

    Help… what stupid little thing am I doing wrong

    April 11th, 2012 at 3:45 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives