Double Click Date Stamp and Time Stamp-CodePudding

I have the below code that generate a date stamp when I double click on a cell column E, and I would like to generate a time stamp in column F double clicking too.

But when I double click in a cell column F I have a compile error stating Ambiguous name detected. (Worksheet_BeforeDoubleClick). But when I try to change it for another name, I can double click in Column F but nothing happens.

Could someone help me please? thanks

`Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("E1:E10000")) Is Nothing Then
    Cancel = True
    Target.Formula = Date
End If
End Sub

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F1:F10000")) Is Nothing Then
    Cancel = True
    Target.Formula = Now() 
End If
End Sub`

CodePudding user response：

You can only have one Worksheet_BeforeDoubleClick handler. So combine the logic:

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Me.Range("E1:E10000")) Is Nothing Then
        Cancel = True
        Target.Value = Date
    ElseIf Not Intersect(Target, Me.Range("F1:F10000")) Is Nothing Then
        Cancel = True
        Target.Value = Now()
    End If
End Sub

As noted in the comments, you can use only one Intersect.

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Me.Range("E1:F10000")) Is Nothing Then
        Cancel = True
        Select Case Target.Column
            Case 5
                Target.Value = Date
            Case 6
                Target.Value = Now()
        End Select
    End If
End Sub