Only want one Worksheet_[event] to run but still cover all actions

[Guest]

Hello again,

I have code ("Sub DoThis" and "Sub DoThisE") that ensures the format in the
sheet. It should run no matter what the end user does with the cells...which
is alot, we all know that...

I now use these Sub Worksheet_[events]: Worksheet_Change,
Worksheet_Activate, Worksheet_Calculate.

I want the code "Sub DoThis" OR "Sub DoThisE" to take place only once, when
an event take place. As it is now an user event start the routines several
times. I believe it is caused by two reasons:
- Worksheet_[event] subs makes an overlap ?
- Application.OnKey / OnEntry makes an overlap?

- How_can_I_ensure the code to run only once every time an event/action take
place of the user on the sheet?
- How_can_I_ensure I cover all actions - select a new cell, paste etc?
- What event cover the action when a cell changes value using a drop down
choice with "Verification & List approach (not as code, used the excel menue
choices)"

The code for each Sub Worksheet_[event] looks the same:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdataing = False

If Not Intersect(Target, Range(Cells(1, 1), Cells(200, 1))) Is Nothing
Then
Call DoThis
Else
Application.OnKey "~", "DoThisE"
Application.OnKey "^v", "DoThis"
Application.OnKey "{ENTER}", "DoThisE"
ActiveSheet.OnEntry = "DoThisE"

End If
Application.OnKey "~", ""
Application.OnKey "^v", ""
Application.OnKey "{ENTER}", ""
ActiveSheet.OnEntry = ""

Application.ScreenUpdataing = False

End Sub


/Regards

 

[Dave Peterson]

You could set up a flag that checks before your routine does anything important:

Dim DoThisWasRun as boolean
sub DoThis()

if dothiswasrun then exit sub
dothiswasrun = true

'the rest of your code here
end sub

This means that it'll run only once -- ever! The first time through, the
default value for the boolean variable is False. You're code changes it to
true. (Actally, this will exit your sub right away--until you change that
boolean variable back to false.)

Is that what you meant?

Maybe dothis was doing things that caused the other events to fire--causing a
recursive loop that goes until excel gets tired.

if that's the case, you can tell excel to stop looking for event triggers with
code like this:

application.enableevents = false
'do as much as you want to whatever you want
'then turn it event handling back on.
application.enableevents = true

Hello again,

I have code ("Sub DoThis" and "Sub DoThisE") that ensures the format in the
sheet. It should run no matter what the end user does with the cells...which
is alot, we all know that...

I now use these Sub Worksheet_[events]: Worksheet_Change,
Worksheet_Activate, Worksheet_Calculate.

I want the code "Sub DoThis" OR "Sub DoThisE" to take place only once, when
an event take place. As it is now an user event start the routines several
times. I believe it is caused by two reasons:
- Worksheet_[event] subs makes an overlap ?
- Application.OnKey / OnEntry makes an overlap?

- How_can_I_ensure the code to run only once every time an event/action take
place of the user on the sheet?
- How_can_I_ensure I cover all actions - select a new cell, paste etc?
- What event cover the action when a cell changes value using a drop down
choice with "Verification & List approach (not as code, used the excel menue
choices)"

The code for each Sub Worksheet_[event] looks the same:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdataing = False

If Not Intersect(Target, Range(Cells(1, 1), Cells(200, 1))) Is Nothing
Then
Call DoThis
Else
Application.OnKey "~", "DoThisE"
Application.OnKey "^v", "DoThis"
Application.OnKey "{ENTER}", "DoThisE"
ActiveSheet.OnEntry = "DoThisE"

End If
Application.OnKey "~", ""
Application.OnKey "^v", ""
Application.OnKey "{ENTER}", ""
ActiveSheet.OnEntry = ""

Application.ScreenUpdataing = False

End Sub

/Regards