Entering (NOW) information on click

[Howard]

Is there any way to put code in the WorkBook Module so that when a person
clicks on a cell (and only when they click on this cell) on any sheet, it
would put in the current time and date?

 

[Dave Peterson]

Not really. Excel doesn't have a _Click event.

But you could use the Workbook_SheetSelectionChange event (which would fire if
you moved the cursor to that cell) or the Workbook_SheetBeforeDoubleClick event
or even the Workbook_SheetBeforeRightClick event.

I chose to use the _sheetbeforeRightClick in this sample.

Option Explicit
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)

Dim myAddr As String
myAddr = "A1"

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time!
End If

If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then
'do nothing
Else
Cancel = True 'don't show options???
Application.EnableEvents = False
With Target
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If

End Sub

 

[Bob Phillips]

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

If Target.Address = "$H$1" Then

Target.Value = Now()
Target.NumberFormat = "dd mmm yyyy hh:mm:ss"
End If
End Sub

 

[Gord Dibben]

Double-click event In Thisworkbook Module

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$1" And Target.Value <> "" Then
Target.Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
End If
Cancel = True
End Sub


Gord Dibben MS Excel MVP

 

[JLGWhiz]

See if this works for you. I used Range("B2"), but you can change that to
whatever you want.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Target.Value = Now
End If
End Sub

 

[Howard]

Thanks to everyone who answered. We really appreciate you folks.