Conflicting event procedures launching at the same time

[angelasg]

I have code in sheet 1 that on deactivation looks for an error on
sheet 1 and if it finds one goes to the cell with the error.

At the same time in the code for ThisWorkbook, I've got code that
before saving stores the current cell location then goes to sheet 3
and does some copying and pasting and then goes back to the starting
cell.

I deliberately put an error in Sheet 1 and then hit the save button.
That launched the "before save" code that goes to sheet 3. That in
turn launched the deactivation code on Sheet 1. Each code is set to
go to different sheets and do stuff. They ended up running
simultaneously and doing something I didn't want them to.

Each code by itself works fine, it's just under certain circumstances
that they run at the same time.

I've been using these event procedures for all of two days. Is there
a way to make them run in a certain order?

Thanks so much in advance.

 

[dbKemp]

I have code in sheet 1 that on deactivation looks for an error on
sheet 1 and if it finds one goes to the cell with the error.

At the same time in the code for ThisWorkbook, I've got code that
before saving stores the current cell location then goes to sheet 3
and does some copying and pasting and then goes back to the starting
cell.

I deliberately put an error in Sheet 1 and then hit the save button.
That launched the "before save" code that goes to sheet 3. That in
turn launched the deactivation code on Sheet 1. Each code is set to
go to different sheets and do stuff. They ended up running
simultaneously and doing something I didn't want them to.

Each code by itself works fine, it's just under certain circumstances
that they run at the same time.

I've been using these event procedures for all of two days. Is there
a way to make them run in a certain order?

Thanks so much in advance.

I don't think you can change the way events fire. You might be able
to have a public boolean variable, say gbInhibitProcessing, (declared
in a standard module) that you can use as a flag. Usually it would be
false, but you set it to true in your event handler, then when the
other event is handled the code checks the value of the boolean before
it does it's job. If true it skips it's job, if false it does it's
job. You would do this in both handlers, and don't forget to change
it back to false.

 

[Dave Peterson]

You can't change the order of events, but you can turn off all event processing
using:

Application.enableevents = false
'code that would cause other events to fire
application.enableevents = true

And most things you do in code don't have to work on the activesheet or use
selections. You can refer to the objects directly.

Dim RngToCopy as range
dim DestCell as range

with worksheets("Somesheetnamehere")
set rngtocopy = .range("a2",.cells(.rows.count,"A").end(xlup))
set destcell = .range("x99")
end with

application.enableevents = false
rngtocopy.copy _
destination:=destcell
application.enableevents = true

And you may be able to avoid saving the current location and
"SomeSheetnamehere"'s worksheet_change event.

ps.

Chip Pearson has a workbook that you can download that will show you the order
of events:
http://cpearson.com/excel/download.htm
look for EventSeq

And some more links...

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[angelasg]

You can't change the order of events, but you can turn off all event processing
using:

Application.enableevents = false
'code that would cause other events to fire
application.enableevents = true

And most things you do in code don't have to work on the activesheet or use
selections.  You can refer to the objects directly.

Dim RngToCopy as range
dim DestCell as range

with worksheets("Somesheetnamehere")
  set rngtocopy = .range("a2",.cells(.rows.count,"A").end(xlup))
  set destcell = .range("x99")
end with

application.enableevents = false
rngtocopy.copy _
  destination:=destcell
application.enableevents = true

And you may be able to avoid saving the current location and
"SomeSheetnamehere"'s worksheet_change event.

ps.

Chip Pearson has a workbook that you can download that will show you the order
of events:http://cpearson.com/excel/download.htm
look for EventSeq

And some more links...

Chip Pearson has some instructions on events:http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:http://www.mvps.org/dmcritchie/excel/event.htm





angelasgwrote:







--

Dave Peterson- Hide quoted text -

- Show quoted text -

I had tried the application.enableevents, but I didn't put them in the
right place. I tried again and it worked fine. Thanks for the help.