Time/Date auto insert upon click
 

Hey all,

I have set up a left double click command to insert time (code below: "BeforeDoubleClick") in rows C and D and I also have a code that inserts the current date one row to the left of column C. However, when I double click in row C, Excel will insert the current time but the code to insert the date one row to the left is ignored. I can get the date code to function if I insert the time in row C by using the CTRL+SHIFT+Colon keyboard shortcut, but I'd prefer to just double click in Row C like I can in Row D.

What am I missing?

Thanks for your help,
Keyrookie


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C:C"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "dddd, mm/dd/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub




Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Const MY_RANGE As String = "C:D"
Dim cell As Date
On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(MY_RANGE)) Is Nothing Then
Target.Value = Format(Time, "hh:mm AM/PM")
End If
Cancel = True
endit:
Application.EnableEvents = True
End Sub