Error when multiple macro driven workbooks open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We currently use several macro driven workbooks. The problem we have
recently encountered is that some users are experiencing fatal errors when
having more than one of these workbooks open.

For instance:
I open WorkbookA.xls and enable macros, select a couple validations, run a
query, look at my results. I then open WorkbookB.xls and do the same thing
but now I get a fatal error or I go to close one of the workbooks and get the
fatal error. Anyone have any idea as to what the problem may be? One of the
errors I have seen is: Excel is encountering a problem and needs to close . .
.. .
 
Theo
Are any of the macros catching events?
they may be getting into a circular triggering effect.

Hard to know for sure without seeing the code, but it is fairly common
for different sets of code to interfere with each other.

cheers
Simon
Blog: www.smurfonspreadsheets.net
 
Simon,

Thanks for the response. If you consider populating the contents of a
validation on open or using the selection of the validation as a variable in
the code then yes we do have catching events in our macros. If you would
please tell me what you mean about "getting into a circular triggering
effect." BTW, we have found in the past that having a combination of certain
files open has caused an issue but we are attempting to make more sense of it
so we can move forward in fixing the issue. Thanks again!
 
Theo
Sorry I should have been clearer.
If you change selection inside a selection_change event it will end in
tears. the routine will recurse (call itself repeatedly)with no way to
escape and will probably crash Excel.

If you are trapping events you really need to use
application.enableEvents = false at the start and = true at the end
Otherwise you may end up triggering other code that may trigger your
event handler again before its finished.

Your workbook open code for example populating the validation list
may invoke a selection_change
a worksheet_activate
a worksheet_deactivate
a calculate
and maybe others, depending how its coded. If code does something in
those events it could eventually get into a circular argument.
hth
Cheers
Simon
Blog: www.smurfonspreadsheets.net
 
Back
Top