Enter current time in a cell by clicking it

Solved/Closed

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

Hello,

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.

Which function will you use to enter current time in a worksheet cell
Which function will you use to enter current time in a woksheet cell? - Best answers
Insert the current date in the selected cell. do not include the current time - Best answers
Which function will you use to enter current time in a worksheet cell? - Excel Forum
Activeworkbook.worksheets current worksheet ✓ - Excel Forum
Transfer data from one excel worksheet to another automatically - Guide
Insert the current date and time in cell a1 ✓ - Excel Forum
How to enter @ in laptop - Guide

7 replies

Answer 1 / 7

ChrisS
Apr 27, 2010 at 04:21 AM

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

=if(A2=1,"Dispatched","")

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

.EntireColumn.AutoFit

End With

End If

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

With Target(1, 3)

.Value = Time

.EntireColumn.AutoFit

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.

Galpizar
Feb 1, 2013 at 05:24 PM

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

Idun9
May 27, 2013 at 03:19 AM

In my code, then it stops at:
If Not Intersect(Target:range.... the first one

It complains at Intersect

ChrisS
May 27, 2013 at 08:59 AM

It was "(target, range" not "(target:range"

avega
Aug 2, 2017 at 06:48 PM

This was great..... thanks

Answer 2 / 7

Helper
Feb 8, 2009 at 08:53 PM

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.

gratch
Jan 7, 2010 at 11:18 AM

I used the code you wrote but for some reason my time is always at midnight, even though PC is not.

king
Feb 5, 2010 at 03:59 AM

hi
how to display current time in cells ,if any shorts key

Savi
Apr 19, 2010 at 06:57 AM

this code is good

Buli
Aug 17, 2010 at 02:03 AM

I WORKED, IT IS GREAT, THANK A LOT !!!! :)))

Answer 3 / 7

Helper
Feb 8, 2009 at 09:08 PM

I forgot to mention. The code works for any cell that is clicked on the worksheet so if you have other info.
that has to be modified this click event will erase what might be in the current cell.

Helper
Feb 8, 2009 at 09:53 PM

Just to clarify. If there are only certain cells that need to be clicked, then something like this will work better.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address

Case Range("A1").Address

Target = Format(Now, "ttttt")

Case Range("A5").Address

Target = Format(Now, "ttttt")

Case Range("A10").Address

Target = Format(Now, "ttttt")

End Select

End Sub

Seek > Helper
Mar 13, 2009 at 01:51 PM

I just used your suggestion for getting times in certain cells and it worked great. Now lets say intead of just cell F4, I want to include F4-F100? How would I change the code. And is there a way to lock them once they are entered? Thank you very much!

WutUp WutUp > Seek
Mar 13, 2009 at 06:27 PM

As you can see, I changed my Nickname. Anyway, let's get to business.

The code below will give you the range F4:F100 as you requested and the sheet protection.
What you need to know also is that everytime you must unprotect the sheet when selecting the range F4:F100. Do this by clicking on the
Review tab and then click on the Unprotect Sheet icon. This will give you access to the range F4:F100
that you want to change. Now, if you immediately click anywhere in the range F4:F100 again, you will see that nothing happens. The reason is that once the code runs it will protect the cells so you don't accidentally click on
another cell. Just unprotect the sheet again in the Review tab.

Hope this helps!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

Target = Format(Now, "ttttt")

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

End If

SkipIt:
Exit Sub

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

End Sub

BradCustom > WutUp WutUp
Apr 6, 2009 at 04:35 PM

I've tried your formula and it works great with one exception; when you try to sort all the fields change to the current date and time. Can the code be changed to a double click on the cell instead of a single click.

Thanks

WutUp WutUp > BradCustom
Apr 6, 2009 at 07:24 PM

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

Answer 5 / 7

Sami
Jul 3, 2010 at 05:33 AM

Hi Fellow,

Dear ChrisS ..

I've tried your macro & it was great.

Anyhow, How can I copy the same macro to become when I type 1 in F2 then the Date will appear on G2 & the Date on H2.

In the end I want that when I type in the A2 the InTime& Date appear in B2 & F2.
And when I type in the F2 the OutTime & Date Will appear in G2&H2

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 3, 2010 at 09:53 AM

Are you sure you want to change B and F col, if A changes. The reason is that you have if if F changes then have G and H changes

Any how, here is the modified code. I have put comments in the code so you should be able to expand the code if needed or modify the code if needed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim sDateCol As String
Dim sTimeCol As String

    'if the cell(s) changed was not in col A or Col F then nothing to do
    If Intersect(Target, Union(Range("A2:A" & Rows.Count), Range("F2:F" & Rows.Count))) Is Nothing Then GoTo End_Sub
    
    ' disable event
    Application.EnableEvents = False
    
    On Error GoTo Error_Handler
    
    ' for every modified cell
    For Each Cell In Target
    
        ' if the modified cell is not in col 1 (A) or at col 6 (F) then go to next changed cell
        If ((Cell.Column <> 1) And (Cell.Column <> 6)) Then GoTo Next_Cell
            
        ' if changed cell is at col 1 (col A)
        If (Cell.Column = 1) Then
            sDateCol = "B"
            sTimeCol = "F"
            
        Else ' default position that changed cell is at col 6 (col F)
            sDateCol = "G"
            sTimeCol = "H"
        
        End If
            
        ' same row but date col
        With Cells(Cell.Row, sDateCol)
        
            .Value = Date
            
            .EntireColumn.AutoFit
        
        End With
        
        ' same row but time col
        With Cells(Cell.Row, sTimeCol)
        
            .Value = Time
            
            .EntireColumn.AutoFit
            
        End With

Next_Cell:
    Next Cell
    
End_Sub:
    Application.EnableEvents = True
    Exit Sub

Error_Handler:
    MsgBox Err.Description
    GoTo End_Sub
    
End Sub

Similar discussions

Current time by clicking comm button in excel

Comparing columns and returning value

Nested IF functions using time values

Excel multiple functions in one cell

If a cell contains any text then add a value

If cell = date return yes or no.

Similar discussions

Subscribe To Our Newsletter!