PDA

View Full Version : running two Worksheet_Change events



MontySharpei
09-16-2008, 10:45 AM
hello,

i saw some code somewhere but cant find it for the life of me now but there was a piece of code written to work around ambigious Worksheet_Change.
Whereby two worksheet change events were performed in the same piece of code. It went something like calling the first piece of code a name(say name 1), and the second another name(name 2).
Then the piece of code preformed name 1 first then carried out name 2. Can anyone enlighten me ??

CreganTur
09-16-2008, 01:10 PM
You could make each piece of code into a user-defined function (or Sub) and then as a part of the Change Event call them in the order you want them to run.

Does that accomplish what you're trying to do?

Bob Phillips
09-16-2008, 02:48 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
' do one type of stuff
End With
ElseIf Not Intersect(Target, Me.Range("D5:D10")) Is Nothing Then
With Target
' do your other stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.