How to fix this "ambiguous name detected Worksheet_Change"?

user_11314588 0
Viewed 188

I'm trying to add two similar macros to do the same things (in two different columns), but when I let it run it says: "Ambiguous name detected Worksheet_Change". (you can find the code below)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("Cost_to_date"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mmm dd, yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("Last_update"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mmm dd, yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

If I try to implement the following to make it smaller, all columns between Cost_to_date and Last_update keep adding dates automatically...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("Cost_to_date","Last_update"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "mmm dd, yyyy"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

They should only change the columns to the right of the two columns named (cost to date and last update), and work individually.

Please let me know what I am missing here.

compiler-errors vba excel
2 Answers

Can you try the below? This seems to be working for me so just wanted to check on your end. This did crash on me when I highlighted and cleared the entire column all at once fyi.

I believe you will need to check the Intersect of each range individually and enter the macro when either one of them is TRUE. From there i'm looping through the Target and making checks accordingly such as

  1. Target is not blank
  2. Target column is correct
Private Sub Worksheet_Change(ByVal Target As Range)

Dim xCell As Range, x As Long, y As Long
x = Range("Last_Update").Column
y = Range("Cost_to_date").Column

If Not Intersect(Range("Last_Update"), Target) Is Nothing Or Not Intersect(Range("Cost_to_date"), Target) Is Nothing Then
    Application.EnableEvents = False
    On Error GoTo SafeExit
    
    For Each xCell In Target
        If xCell.Column = x Or xCell.Column = y Then
            If xCell <> "" Then
                xCell.Offset(, 1) = Now
            Else
                xCell.Offset(, 1).ClearContents
            End If
        End If
    Next xCell
    
    Application.EnableEvents = True
    Target.Offset(, 1).NumberFormat = "mmm dd, yyyy"
    
End If
Exit Sub

SafeExit:

Application.EnableEvents = True
MsgBox "Error Occured", vbCritical

End Sub
user_67064190

You can only have one Worksheet_Change event per worksheet. The short answer is you need to combine these to one event OR convert the actions to macros and modify your event to dynamically call the action macros.

Consider the below approach which contains one Worksheet_Change event and 3 sub routines. Notice the only job of the change event is to determine what actions need to be taken and then call the appropriate subroutine (which a single sheet can have many of unlike events). The criteria your event would test is the Intersect - when TRUE, you simply pass the Target into the subroutine and do the actions there.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

    If Condition_1 Then
        Macro_1 (Target)
    ElseIf Condition_2 Then
        Macro_2 (Target)
    ElseIf Condition_3 Then
        Macro_3 (Target)
    End If
    
Application.EnableEvents = True

End Sub

Sub Macro_1(Target As Range)
    'When condition_1 is met
End Sub

Sub Macro_2(Target As Range)
    'When condition_2 is met
End Sub

Sub Macro_3(Target As Range)
    'When condition_3 is met
End Sub
user_67064190
Related