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?
--
Howard

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



Howard wrote:

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?
--
Howard

--

Dave Peterson

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


--

HTH

Bob

"Howard" wrote in message
...
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?
--
Howard

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

On Fri, 2 Apr 2010 08:23:02 -0700, Howard
wrote:

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?

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" wrote in message
...
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?
--
Howard

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


"JLGWhiz" wrote:

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" wrote in message
...
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?
--
Howard


.