Kioskea
Recherche
Ask a question »

Excel - Enter current time in a cell by clicking it

March 2015


Excel - Enter current time in a cell by clicking it





Issue


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.

Solution


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

OR

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 

Else 

ActiveSheet.Unprotect 
Application.ScreenUpdating = False 

Target = Format(Now, "ttttt") 

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

End If 

ActiveSheet.Unprotect 
Rows("1:3").Select 
Range("1:3,A4:E65536").Select 
Range("1:3,A4:E65536,G4:IV65536").Select 
Selection.Locked = False 
Selection.FormulaHidden = False 
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ 
False 

ActiveCell.Offset(, 1).Select 

SkipIt: 
Exit Sub 


End Sub


Solution by WutUp WutUp
For unlimited offline reading, you can download this article for free in PDF format:
Excel-enter-current-time-in-a-cell-by-clicking-it.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - Enter current time in a cell by clicking it » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.