Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need help setting up macro that runs each time I load Excel 2007. I have tried following a few walkthroughs on the web but they end up not autolaunching the macro.

share|improve this question
Just to clarify... are you talking about a macro that runs when you open Excel or a script that launches Excel and does something. Either way, expect this question to be migrated to stackoverflow, where programming questions are best answered. – variant Apr 29 '11 at 22:27
Sorry so long to reply. I am talking about an excel macro that runs when excel is launched. – mike G May 9 '11 at 15:42
up vote 3 down vote accepted

You need to have the correct code in the correct place in a specific workbook saved in a specific location to run something when you load Excel.

To do this:

  1. Press Alt+F11 to load the VB Editor

  2. On the left side, you should see the Project Explorer, which is a tree menu of your open workbooks and their coding elements. Press F4 if you do not see this menu.

  3. In the Project Explorer pane, choose the project corresponding to your current workbook and expand out Microsoft Excel Objects under this project. Double-click ThisWorkbook to view the code that runs when the workbook is open, closed, deactivated, etc.

  4. A macro that runs when the workbook opens needs to look like this:

    Private Sub Workbook_Open()
       Msgbox "Yes, it worked.", vbinformation + vbokonly, "Macro Launch Successful"
    End Sub
  5. Now, close the VB Editor, but leave Excel open.

  6. Since you want this to run whenever Excel loads, you need to save this workbook as "Personal.xlsm" into the following directory (copy this address to your clipboard then paste it in the Filename field in the Save As dialog): %appdata%\Microsoft\Excel\XLStart\Personal.xlsm

  7. With the document saved (but still open!), click the View tab in Excel's ribbon and click the "Hide" button to hide the workbook. If you don't do this, Personal.xlsm will always appear when you load Excel. Hiding it allows it to be open, but not visible (obv.).

  8. Now close Excel and click Yes to save changes to Personal.xlsm.

  9. Reopen Excel to test.

Let me know if this doesn't work.

share|improve this answer
Thanks variant. It works. This totally helped. – mike G May 11 '11 at 15:47
@mike G - Glad to help, Mike. Happy coding. – variant May 11 '11 at 20:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .