One click time

  • Thread starter Thread starter doss04
  • Start date Start date
D

doss04

I know the question has been asked and the simple answer is ctrl - shift - :
to add time in a cell. Unfortunatly sometimes the easy solution is still
difficult. I would like to know how to click on a cell and have the time
appear. Because i do security patrols and my laptop is mounted in my vehicle.
It"s difficult to type unless i stop to look at the keyboard and it's still
typing with one hand. If anyone is willing to tell that would be great. I am
new to excel and i am willing for trial and error. Thanks
 
Hi,

It colud be done like this. Right click your sheet tab, view code and paste
this in on the right. Change the 5 (Column E) to the appropriate column. It
can be written so that this only happens in certain cells and if you need
more help post back.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
If IsEmpty(Target) Then Target.Value = Time
End If
End Sub

Mike
 
There is no click event that you can tie into. You could use the
_selectionchange event that Mike suggested, but you may find that you're
changing things you don't want changed.

I'd recommend either the _beforerightclick or _beforedoubleclick event.

I'd use one of these (not both):

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Cancel = True

'doubleclick anywhere, but only column E can change
With ActiveCell.EntireRow.Range("e1")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True

'rightclick anywhere, but only column E can change
With ActiveCell.EntireRow.Range("e1")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With

End Sub

Rightclick on the worksheet tab that should have this behavior and select view
code. Paste this (one of them!) into the code window that opens up.

Then back to excel and test it.
 
Thanks it worked. But when i tried to do another column it popped up with an
error. basicly saying i can't do it the way i did. So how would i go about
doing 2 columns
 
Disregard last post. I figured it out. Thanks, you guys are really helping
make my job easier and more efficient.
 
I figured out how to do both columns. i think i missed a step somewhere. I
closed out the worksheet and saved the changes. But when i opened it again it
didn't work. I looked on the screen where i typed the changes, the changes
was still there. but it didn't work. Itried to retype it. Nothing the cells
are still formatted hh:mm. But the clicking on the cell is gone. Any
suggestions? Thanks
 
Back
Top