Compile error: Ambigious name detected: Worksheet_Change **NEWBIE**

[Dan]

Hi all -
I am getting a Compile error: Ambigious name detected: Worksheet_Change

message when processing my code.

From reading various posts, I see that I can only use the

Worksheet_Change process once.

In my code below, how can I combine these procedures so I can make this
work? Thanks for your help!

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "d5"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Range("c6:c24").Copy Range("c7:c25")
Range("c6").Value = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "q5"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Range("p624").Copy Range("p725")
Range("p6").Value = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[paul.robinson]

Hi
This should work:
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Target.Address = "D5" Then
Range("c6:c24").Copy Range("c7:c25")
Range("c6").Value = Target.Value
ElseIf Target.Address = "Q5" Then
Range("p624").Copy Range("p725")
Range("p6").Value = Target.Value
End If
Application.EnableEvents = True
ws_exit:
Application.EnableEvents = True
End Sub

I don't see the need for the EnableEvents or error code?

regards
Paul

 

[Norman Jones]

Hi Paul,

If Not Target.Address = "D5" Then

I suspect that an exraneous 'Not' has crept into your code. Additionally, I
suspect that you intended "$D$5" rather than "D5".

Similarly, I assume that

ElseIf Target.Address = "Q5" Then

was intended as:

ElseIf Target.Address = "$Q$5" Then

I don't see the need for the EnableEvents or error code?

If events are not disabled, the copy operation(s) will re-trigger the
Worksheet_Change event and it is good practice to restore Application
settings in an error handler. Having, however, restored the Events setting
in the error handler, it is not necessarry to additionally restore Events
immediately prior to the error handler.

 

[Dan]

This was great! Thanks Paul and Norman for your help.