Double Click cell inserts Today's Date (1 Viewer)

[Danick]

Here's a simple code to insert today's date in a cell in Column "E" when double clicking any cell in that column. It works fine, however, I can't figure out how to make it start at Row 3 of that column. Any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("E:E")) Is Nothing Then
Cancel = True
Target.Value = Now()
End If
End Sub

 

[Pat Hartman]

Excel is not my tool of choice so who knows if this would work. How about locking the first three rows which I presume are headers? Or, if there is nothing in the header for that column, change the font color to hide text in the first three rows.

 

[Danick]

Excel is not my tool of choice so who knows if this would work. How about locking the first three rows which I presume are headers? Or, if there is nothing in the header for that column, change the font color to hide text in the first three rows.

Unfortunately, locking the header also won't allow filtering. Actually, that's the problem since double clicking the header also replaces the Column header with today's date. I may have found a work around for now by changing the range to Range("E3:E1000"). But I'm crazy about it...

 

[CJ_London]

If you want today’s date why not use the date function rather than now (which also includes the time)

 

[Danick]

If you want today’s date why not use the date function rather than now (which also includes the time)

Would you please elaborate on how using the date function would resolve my issue about inserting Today's date by double clicking a cell?

 

[Edgar_]

I can't figure out how to make it start at Row 3 of that column. Any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Target.Row >= 3 Then
            Cancel = True
            Target.Value = Now()
        End If
    End If
End Sub

 

[CJ_London]

it doesn't - it's just about using the right tool for the job.

 

[Danick]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
        If Target.Row >= 3 Then
            Cancel = True
            Target.Value = Now()
        End If
    End If
End Sub

It's working perfectly. Still can't believe how simple the fix was.
Thanks so much for your help.