How to make 2 worksheet events work together

[Shazam]

I would to to make 2 worksheet events how would I go about that ? Meaing I have this code:

[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("G50")

Range("A" & Target.Row) = Now()

End Sub[/VBA]

But then I have another range I would like to do:

[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("C29")

Range("D" & Target.Row) = Now()

End Sub[/VBA]

It does work togther but I would like to have this way to be seperated. Is there a way we can make this work ?

I found this but I cant get it to work.


[VBA] Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Call ChangeEvent1(Target)
Call ChangeEvent2(Target)
End Sub

Private Sub ChangeEvent1(ByVal Target As Range)
Dim rng As Range
Set rng = Range("G50")

Range("A" & Target.Row) = Now()
End Sub

Private Sub ChangeEvent2(ByVal Target As Range)

Dim rng As Range
Set rng = Range("C29")

Range("D" & Target.Row) = Now()
End Sub
[/VBA]

Any Ideas ?

[mdmackillop]

Hi Shazam,
Not totally clear on your requirements, but check the following alternatives.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Intersect(Target, Union([G50], [C29])) Is Nothing) Then
Range("A" & Target.Row) = Now()
Range("D" & Target.Row) = Now()
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case Is = "G50"
Range("A" & Target.Row) = Now()
Case Is = "C29"
Range("D" & Target.Row) = Now()
End Select
Application.EnableEvents = True
End Sub

[/VBA]

[Bob Phillips]

I think that this is what you want

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C29,G50"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Range("A" & .Row) = Now()
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[Shazam]

Thank You both for replying.


I found this link:

http://www.ozgrid.com/forum/showthre...et+event+range

Can I have multiple worksheet event codes in this manner ?

[Bob Phillips]

If you want to be able to react to same way to two different ranges, use the code I gave you.

If you want react differently to multiple ranges, look at Malcolm's Select Case code for the method.

If yopu want to do two things upon a change event, just do one after the other.

I can see no rationale for doing it as shown there.

[Shazam]

Ok I thought will be easier to read the codes. I do what you suggested.

But is it possible doing in that manner ?


Thanks!

[johnske]

Ok I thought will be easier to read the codes. I do what you suggested.

But is it possible doing in that manner ?


Thanks!

Yes you can call a procedure (or two...) from a worksheet event, and this may be beneficial (for reading/memory purposes) if you have very lengthy procedures or if the same procedure is also used separately elsewhere in the workbook.

But for what you're doing that only complicates matters,

If you want to be able to react to same way to two different ranges, use the code I gave you.

If you want react differently to multiple ranges, look at Malcolm's Select Case code for the method.

If yopu want to do two things upon a change event, just do one after the other.

I can see no rationale for doing it as shown there.

i.e. Just keep it as simple as possible is the best policy...

[Shazam]

Thank You for replying.

Your right it will probably get more complicated. My curiosity is if its possible doing that way.