A few words of thanks would be greatly appreciated.

Excel - Enter current time in a cell by clicking it


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.


I think this is what you are looking for, but let me explain where the code must reside.
Since you want the time to happen by a click event you must put the code in the
Worksheet SelectionChange event in the worksheet itself and not just create a macro.
You stated you are using Office 2007 so here is what you do.
  • 1) Click on the Developer Tab.
  • 2) Click on the Visual Basic icon at the top left of the ribbon.
  • 3) Now on the left pane window, you will see a folder called Microsoft Excel Objects that should be expanded already with the worksheet names of your workbook.
  • 4) Double click on the worksheet where you want the time to appear.
  • 5) Now you will see two drop down boxes; on the left you will see (General), and on the right you will see (Declarations).
  • 6) Click on the left drop down and change (General) to Worksheet.
  • 7) Now you will see Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  • 8) This is the event you need for the mouse click. You will only have to click the mouse once.
  • 9) Copy and paste this code between Private Sub Worksheet_SelectionChange(ByVal Target As Range)

and End Sub.

If Target.Address = ActiveCell.Address Then 

Target = Format(Now, "ttttt") 

End If 
  • 10) Close the Visual Basic Editor and give it a try.

Solution by Helper


You will need to put the code in the Worksheet Before Double click AppEvent instead of the Selection
Change event. You will probably notice it takes slightly longer for the code to run when you double click.

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

Dim MyRange As Range 
Dim IntersectRange As Range 

Set MyRange = Range("F4:F100") 

Set IntersectRange = Intersect(Target, MyRange) 

On Error GoTo SkipIt 

If IntersectRange Is Nothing Then 
Exit Sub 


Application.ScreenUpdating = False 

Target = Format(Now, "ttttt") 

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
ActiveSheet.EnableSelection = xllockedCells 

End If 

Selection.Locked = False 
Selection.FormulaHidden = False 
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ 

ActiveCell.Offset(, 1).Select 

Exit Sub 

End Sub

Solution by WutUp WutUp

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.


This document, titled « Excel - Enter current time in a cell by clicking it », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).