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
See also
Knowledge communities.
