Worksheet with Multiple Worksheet Changes

[roccobusso]

Hi
I have a worksheet which I would like to add 3 different change requests as per below
I get below message so I gather this may not be possible

Compile error:
Ambiguous name detected: Worksheet_change


Does anyone have any tips on how to get around this.
I am only new to Excel code so help much appreciated

Thanks
Rocco


CODE
----------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C16:C29")) Is Nothing Then Exit Sub 'specific range

'Turn off events to keep out of loops
Application.EnableEvents = False

v = Application.Match(Target.Value, Worksheets("Lists").Range("B:B"), False)

If Not IsError(v) Then
Target.Value = Worksheets("Lists").Range("A:A").Cells(v).Value
End If

'Turn events back on to get ready for the next change
Application.EnableEvents = True
End Sub
----------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("G16:G29")) Is Nothing Then Exit Sub 'specific range

'Turn off events to keep out of loops
Application.EnableEvents = False

v = Application.Match(Target.Value, Worksheets("Lists").Range("F:F"), False)

If Not IsError(v) Then
Target.Value = Worksheets("Lists").Range("E:E").Cells(v).Value
End If

'Turn events back on to get ready for the next change
Application.EnableEvents = True
End Sub
----------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H16:H29")) Is Nothing Then Exit Sub 'specific range

'Turn off events to keep out of loops
Application.EnableEvents = False

v = Application.Match(Target.Value, Worksheets("Lists").Range("I:I"), False)

If Not IsError(v) Then
Target.Value = Worksheets("Lists").Range("H:H").Cells(v).Value
End If

'Turn events back on to get ready for the next change
Application.EnableEvents = True
End Sub
Quote ReplyReport Edit

[TMS]

For each one, you need to change this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C16:C29")) Is Nothing Then Exit Sub 'specific range

'Turn off events to keep out of loops
Application.EnableEvents = False

v = Application.Match(Target.Value, Worksheets("Lists").Range("B:B"), False)

If Not IsError(v) Then
Target.Value = Worksheets("Lists").Range("A:A").Cells(v).Value
End If

'Turn events back on to get ready for the next change
Application.EnableEvents = True
End Sub

To this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C16:C29")) Is Nothing Then ' Exit Sub 'specific range

'Turn off events to keep out of loops
Application.EnableEvents = False

v = Application.Match(Target.Value, Worksheets("Lists").Range("B:B"), False)

If Not IsError(v) Then
Target.Value = Worksheets("Lists").Range("A:A").Cells(v).Value
End If

'Turn events back on to get ready for the next change
Application.EnableEvents = True
End If
End Sub

And combine into one event handler.

You will only need this once:

Dim v As Variant

If Target.Cells.Count > 1 Then Exit Sub

[roccobusso]

Thanks for that work great much appreciated

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.