Enter current time in a cell by clicking it

ncturtle - Feb 8, 2009 at 01:51 PM
 avega - Aug 2, 2017 at 06:48 PM

I'm trying to minimize the effort to input data in a sheet I've built for recording time for dispatched workers I need to find a way to fill a cell with the current time by just clicking or double clicking on it. The idea being that a technician can see the details of a work order, then just click on the appropriate cell to record their dispatch time.

I'm a noob to functions and VB but I'm familiar with Excel, I need to know where to start. Thanks

I'm using office 2007, if that makes any difference.

7 responses

Check this one out. It worked great for me! If anything you would just have to have a blank cell with something like:

Assuming A2 as the input cell:

Type this in D2


Then have your guys just type a "1" when they dispatch.

Then right click on the spreadsheet tab. Select "View Code"

Copy and paste this:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then

With Target(1, 2)

.Value = Date


End With

End If

If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then

With Target(1, 3)

.Value = Time


End With

End If

End Sub

---------------------------------------------------- (without the lines though).

Every time they type a 1 on A2 and hit "Enter" the date will appear on B2, time on C2 and "Dispatched" on D2.

Hope this helps.
It work great ... thanks alot !!!
Genius21 Posts 1 Registration date Tuesday March 5, 2013 Status Member Last seen March 5, 2013
Mar 5, 2013 at 03:50 PM
I like this, it works brilliantly.
Can you please show me, if I am using date in C2 and frequency in D2(in months variable) and like to update in E2
In my code, then it stops at:
If Not Intersect(Target:range.... the first one

It complains at Intersect
It was "(target, range" not "(target:range"
This was great..... thanks