Workbook_open event disabled by manual calc command

[Twotone]

I am using Excel 2003 on Windows XP professional.

I have some code that runs every time a workbook is opened. As you would
expect, it makes no difference to the firing of this event whether the
application is set to manual or automatic calculation mode.

However, when I change from automatic to manual calculation using the
following line of code in an unrelated procedure, the Workbook_open event
handler stops working until I close and re-load Excel.

Application.Calculation = xlCalculationManual


Any ideas why this is and what to do about it?

TIA

Anthony

 

[John Coleman]

I can't reproduce your problem. Are you sure that the procedure which
changes the application.calculation value doesn't also change
Application.EnableEvents?
Maybe you can do a project-wide search for occurences of
"Application.EnableEvents"

Is the problem is showing up when you invoke Workbooks.Open() to open
a workbook with a workbook_open event-handler? Maybe before the
offending line you can put the line msgbox Application.EnableEvents
prior to the Open() command to see if the EnableEvents setting has
somehow been changed. If it has been and for some reason you can't
track down just where it was changed a work-around might be to use the
2 lines
ApplicationEnableEvents = True
Workbooks.Open("myfile.xls")

Finally, in the workbook that you are trying to open, have you tried
putting something like msgbox "I'm here!" at the very start of the
workbook_open() sub? It is possible that execution is reaching there
but that your other code has changed something else (other than the
calculation mode) causing the open event to fire but to not work as
usual. If you have some sort of error-trapping it is possible that
this could go unnoticed.

Just a few stabs in the dark. I'm sure you've tried similar things.

-John Coleman

 

[Twotone]

Thanks for the suggestions. To be clear, I need my event to run every time
the user manually opens any workbook they happen to have access to (The
procedure then stores information about each workbook that is opened). I am
therefore not using Workbook.Open().

I have checked that EnableEvents is True and remains True (a) when my
procedure that includes "Application.Calculation = xlCalculationManual"
starts and ends; and (b) when the event-handler starts and ends.

I think when an event like Workbook_Open occurs, there are actually several
"things" that occur and they occur in a particular order. I can't remember
what they are but am wondering if it is one of those that is being affected
and stopping the event handler from working. Does this ring any bells?

TIA

Anthony

 

[John Coleman]

Sounds like you are wanting "Application level events." With ordinary
events, there is no reason for code in workbook A to fire
automatically when workbook B is opened - but if you have enabled
application-level events in A, code in workbook A (assuming it is open
of course) *can* fire automatically when any other workbook is opened.
I have never used such events, but, as in many things, Chip Pearson's
website is a good source of information: http://www.cpearson.com/excel/AppEvent.htm
(you can also look at the online help of course)

Hth

-John Coleman