Auto_Open vs Open and call macro --priority question

  • Thread starter Thread starter John Baker
  • Start date Start date
J

John Baker

Hi:

I have a spreadsheet called "collector.xls" that contains a macro "auto_open", which is
intended to execute when ever the sheet is opened. I am calling this sheet from another
sheet (opening the sheet in question and then calling a macro in the sheet I just
opened"), as in



Workbooks.Open Filename:="C:\IPT\timesheets\Collector.xls"
Application.Run "Collector.xls!Collectorcheckandmove"

My question is whether the auto-open macro in the collector will execute before the macro
I am calling from the external spreadsheet.

Thanks for he response

John Baker
 
Hi John

Auto_open won't run at all when you open from code. Unless you request it to run like
this:

ActiveWorkbook.RunAutoMacros (xlAutoOpen)

These things are pretty easy to test for yourself with something like

Sub Auto_open()
MsgBox "HI from Auto_open"
End Sub
 
John Baker said:
Hi:

I have a spreadsheet called "collector.xls" that contains a macro
"auto_open", which is
intended to execute when ever the sheet is opened. I am calling this sheet
from another
sheet (opening the sheet in question and then calling a macro in the sheet I
just
opened"), as in



Workbooks.Open Filename:="C:\IPT\timesheets\Collector.xls"
Application.Run "Collector.xls!Collectorcheckandmove"

My question is whether the auto-open macro in the collector will execute
before the macro
I am calling from the external spreadsheet.
Click to expand...


Actually, the auto-open macro won't run at all. Auto-macros will run
if the file is opened directly (via the file menu or from the
Finder), but will not run when opened from code.

To get automacros to run, you need to use


Workbooks.Open Filename:="C:\IPT\timesheets\Collector.xls"
ActiveWorkbook.Autorun xlAutoOpen
Application.Run "Collector.xls!Collectorcheckandmove"

Alternatively, you can run macros on opening (whether from code or
direct) by calling it from, or including it in, the opened
workbook's Workbook_Open() event. Put this in the ThisWorkbook code
module:

Private Sub Workbook_Open()
MyOpenMacro 'or include macro here
End Sub

In general, if opening directly, Auto_Open macros run after all
event macros associated with opening a file, but before your
Application.Run call.

To see the order of firing:

http://cpearson.com/excel/events.htm
 
Just and added comment.
ActiveWorkbook.RunAutoMacros (xlAutoOpen)
really shouldn't have parens around the argument since you are not returning
a value. It doesn't cause a problem here, but could if the argument were an
object.

ActiveWorkbook.RunAutoMacros xlAutoOpen
 
Back
Top