Macro to run when w/b opens (Excel ’97)

Topic

New Reply

My brain has already left for the weekend.

Please tell me what I need to call a macro to have it run automatically when I open a certain workbook.

I thought it was Auto_Open, but nothing happens.

Reply | Quote

Replies

Auto_Open is OK ,but is there for backward compatibility (from xl5 days). It does not run when a workbook is opened using VBA code.

Better is the Workbook_open event in the thisworkbook module.

Reply | Quote

In 97+ best to use the Private Sub Workbook_Open() event at the Workbook level. In the VBE Project Explorer window (left side), open the Microsoft Excel Objects for the workbook, double click the Workbook Object, in the code pane Object drop-down (upper left) select Workbook and (usually it will be created for you, but if not) from the Procedure drop-down (upper right) select Open. See also this thread.

Reply | Quote

Bingo! Thanks guys.

Reply | Quote

Workbook_Open is the way to do this, but there is a risk associated with using it as it relies on events being enabled. In practice, you can’t guarantee that another Workbook/Add-In has left events enabled.
For this reason, I always put an Auto_Open into my Excel projects, make Workbook_Open public, and call Workbook_Open from the Auto_Open. I also make sure Workbook_Open sets a flag (Class scope Boolean) to prevent it from running twice.
Some may consider this overkill, but in a situation where you really need to make sure your Workbook_Open code runs, and you have no control over other VBA that the users run in other Workbooks or Add-Ins then it is, unfortunately, necessary.

Reply | Quote

I like this idea as occasionally events have gotten disabled and I wasted too much time figuring out why my program isn’t running. I’ve never used Auto_Open as I started VBA in xl97. Where does it go? I tried adding it in ThisWorkbook but it didn’t run (yes it was public).

public sub Auto_Open
  debug.print "in Auto_Open"
end sub

I then did the same debug.print statement in Workbook_open but when I then saved,closed, re-opened the WB I only saw the print message for WB open not Auto_Open. I then put Auto_Open in a Module but it didn’t get fired first either.

Deb

Reply | Quote

Auto_Open() should be placed in a general module, e.g. Module1. If you have both the Workbook_Open event and Auto_Open coded, Workbook_Open runs first, but under normal circumstances both should execute.

Andrew C

Reply | Quote