PDA

View Full Version : How to make 2 worksheet events work together



Shazam
12-18-2005, 02:44 PM
I would to to make 2 worksheet events how would I go about that ? Meaing I have this code:


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

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

End Sub

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

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

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

End Sub

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.


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


Any Ideas ?

mdmackillop
12-18-2005, 03:16 PM
Hi Shazam,
Not totally clear on your requirements, but check the following alternatives.

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

Bob Phillips
12-18-2005, 03:26 PM
I think that this is what you want


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

Shazam
12-18-2005, 03:41 PM
Thank You both for replying.


I found this link:

http://www.ozgrid.com/forum/showthread.php?t=42303&highlight=Worksheet+event+range

Can I have multiple worksheet event codes in this manner ?

Bob Phillips
12-18-2005, 03:58 PM
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
12-18-2005, 04:10 PM
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
12-18-2005, 04:47 PM
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
12-18-2005, 05:16 PM
Thank You for replying.

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