Windows – Run macro upon opening Excel 2007

macrosmicrosoft-excel-2007vbawindows

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.

Best Answer

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.

Related Question