Auto Enter Date & Time on Click

[BradCustom]

Hi,

I'm trying to have a cell auto fill with the current date and time; I can get it to work with a single click but I'm not sure how to change the code to require a double click. Below is the code that works for a single click.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange As Range
Dim IntersectRange As Range

Set MyRange = Range("H3:I2300")

Set IntersectRange = Intersect(Target, MyRange)

On Error GoTo SkipIt

If IntersectRange Is Nothing Then
Exit Sub

Else


Target = Format(Now, "mm/dd/yy hh:nn")

End If

SkipIt:
Exit Sub

End Sub

Any ideas on how to change this to require a double click would be greatly appreicated.

Thanks!!

 

[SkipVought]

Hi,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Value = Now
End Sub

Paste this into your sheet object code window.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here it is with the control logic...

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

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[BradCustom]

Thanks Skip,

Your second suggestion worked just fine!

 

[AnotherHiggins]

This isn't a VBA solution, but FYI:

[Ctrl]+[;] inserts the current date into a cell
[Ctrl]+[Shift]+[;] inserts the current time into a cell

[Ctrl]+[;] , [Space] , [Ctrl]+[Shift]+[;] will enter the current date and time.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.