Ambiguous name detected error

Why do I have that error?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Target.Column = 19 And Target <> "" Then
    If Target.Offset(0, 4) = "" Then Target.Offset(0, 4).Value = Now
End If

If Target.Column = 19 And Target = "" Then
    Target.Offset(0, 4).ClearContents
End If

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Column = 5 And Target <> "" Then
    If Target.Offset(0, 18) = "" Then Target.Offset(0, 18).Value = Now
End If

If Target.Column = 5 And Target = "" Then
    Target.Offset(0, 18).ClearContents
End If

End Sub

It's ambiguous because you have *two* "Private Sub Workbook_SheetChange" events.

You need to combine the tests within one "Private Sub Workbook_SheetChange" event.

Regards

One way:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Target.Column = 19 And Target <> "" Then
    If Target.Offset(0, 4) = "" Then Target.Offset(0, 4).Value = Now
    Exit Sub
End If

If Target.Column = 19 And Target = "" Then
    Target.Offset(0, 4).ClearContents
    Exit Sub
End If

If Target.Column = 5 And Target <> "" Then
    If Target.Offset(0, 18) = "" Then Target.Offset(0, 18).Value = Now
    Exit Sub
End If

If Target.Column = 5 And Target = "" Then
    Target.Offset(0, 18).ClearContents
    Exit Sub
End If

End Sub

Regards

In actual fact, each section should be structured like:

If Target.Column = 19 And Target <> "" Then
    If Target.Offset(0, 4) = "" Then 
        Application.EnableEvents = False
        Target.Offset(0, 4).Value = Now
        Application.EnableEvents = True
    End If
    Exit Sub
End If

This is because you are making changes to the sheet within the change event.


Regards

In what way didn't it work?

I would have expected it to work but it would have gone through the routine more times than it needed to.

The second example looks OK ... but, if the first attempt failed, I don't see why this would be any more successful.

Regards

This works as I would expect it to:

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Column = 19 And Target <> "" Then
    If Target.Offset(0, 4) = "" Then
        Application.EnableEvents = False
        Target.Offset(0, 4).Value = Now
        Application.EnableEvents = True
    End If
    Exit Sub
End If

If Target.Column = 19 And Target = "" Then
    Application.EnableEvents = False
    Target.Offset(0, 4).ClearContents
    Application.EnableEvents = True
    Exit Sub
End If

If Target.Column = 5 And Target <> "" Then
    If Target.Offset(0, 18) = "" Then
        Application.EnableEvents = False
        Target.Offset(0, 18).Value = Now
        Application.EnableEvents = True
    End If
    Exit Sub
End If

If Target.Column = 5 And Target = "" Then
    Application.EnableEvents = False
    Target.Offset(0, 18).ClearContents
    Application.EnableEvents = True
    Exit Sub
End If

End Sub

Regards

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

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