Insert Time with Macro

[AdminCo]

Hello,

What I am trying to do is use a macro to enter specific information on the current row ie. today's date, the time as at the time of entry etc. The date is easy as I have the macro enter =today() and then go back Copy | Paste Special | Value with the same macro.

What I would like is that the time enters as it is at that particular moment. That is to say that at 12:08 that the cell remains as 12:08 but if I play the macro at 4:35 that it returns 4:35 not 12:08. How does one get around this? Any help would be appreciated

 

[Andrew Poulsom]

The TODAY() function returns the date as an integer. Use the NOW() function for data and time.

Better still use VBA's Now function which avoids the need for Copy/Paste Special:

ActiveCell.Value = Now

 

[AdminCo]

Hello Andrew,

The date is in one cell and the start and end time in two other cells ie. Cell A2 has the date, Cell B2 has the start time, Cell C2 the end time and say Cell D2 has the calculation =(C2-B2)*24. I guess I could have the macro enter =now() and go back and copy/paste special and format at the same time.

Unfortunatley, VBA is Greek to me so I am not sure what it is that you stated (ActiveCell.Value = Now) and do not know where to start with that. I guess I might as well begin. I ordered the book MrExcel on Excel because I understand the last three chapters deal with VBA but the books have not yet arrived.

Regards,

Alan

 

[Brian from Maui]

Hello,

What I am trying to do is use a macro to enter specific information on the current row ie. today's date, the time as at the time of entry etc. The date is easy as I have the macro enter =today() and then go back Copy | Paste Special | Value with the same macro.

What I would like is that the time enters as it is at that particular moment. That is to say that at 12:08 that the cell remains as 12:08 but if I play the macro at 4:35 that it returns 4:35 not 12:08. How does one get around this? Any help would be appreciated

Not sure if this is what you want. Just double click in the cell for the date/time. I forgot who I got this from too!...

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

Set isect = Application.Intersect(Target, Range("B1:F100"))
If Not (isect Is Nothing) Then
Target.Value = Now
End If

End Sub

 

[Whisperer14]

Hi Alan,

To achieve what I think that you want then post the code that follows into the appropriate Sheet of your workbook then will become , I hope.

Click in the Column B between the second and the first empty row to get it to work. I used column headers of Today, Date, Start Time, Finish Time and Elapsed Time in A1 through to E1.

Got a problem with Colo's utility so can not give you a picture.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim iRow As Integer
    Dim iSect As Range
    'Find the first blank row in Column B
    iRow = Range("B65536").End(xlUp).Row + 1
    'Set the valid search range to be the first cell to the first blank cell
    Set iSect = Application.Intersect(Target, Range("B2", "B" & iRow))
    If Not (iSect Is Nothing) Then
        'Reset the value of iRow
        iRow = Target.Row
        'You can hide column A
        Range("A" & iRow).Value = Now
        'Checks to see whether this is a first click or subsequent click, if
        ' a first then...
        If Range("B" & iRow) = "" Then
            Range("B" & iRow).Value = Now
            'Set the format for the B Cell to Fri Mar 14 or whatever takes your fancy
            Range("B" & iRow).NumberFormat = "ddd mmm dd"
            Range("C" & iRow).Value = Now
            Range("C" & iRow).NumberFormat = "h:mm"
        'If a subsequent click and the D column has no entry
        ElseIf Range("D" & iRow) = "" Then
            Range("D" & iRow).Value = Now
            Range("D" & iRow).NumberFormat = "h:mm"
            Range("E" & iRow).Value = _
                Range("D" & iRow).Value - Range("C" & iRow).Value
        'If Cells C & D contain data and there is a subsequent click in a B cell
        ' then the only cell that will update is the A cell
        End If
    End If
End Sub

HTH Gordon

 

[Whisperer14]

Alan,

I had not spotted your unfamiliarity with VBA! To play with the above code start with a new workbook and then Right-click on the tab of the first sheet and select 'View code' from the menu.

Select the code in the post above and then Copy and Paste into the blank VBA sheet. Select Alt+Q to get back to Excel and then put in your Headers as above, finally click anywhere on the sheet to confirm that nothing happens and then click in B2 to start the ball roling.

To get the finished time you must click anywhere other than B2 and then back on B2 again. This is because the procedure that you copied is one that works when there is a selection change occuring and 2 clicks into the same cell do not count.

Best wishes

Gordon

 

[AdminCo]

Hello Gentlemen,

Thank you very much for your feedback, advice and input. As users of VBA do you write the VBA from scratch or like a macro is it recorded and pasted?

This is truly a most incredibly intriguing aspect of Excel (and any VBA based programs) and definitely opens a ton of new doors and opportunities. Yes, kind of like knowing that there is no less than another twenty-year learning curve at this intersection.

I ended up using Gordon’s code. As I mentioned earlier I am awaiting arrival of the MrExcel on Excel but would like to ask you folks what you recommend as a good source of information (written material) for somebody who has no experience with VBA. Just looking at some of the samples should keep me busy for a couple of months.

Again many thanks for all your help.

Regards,

Alan

 

[PaddyD]

anything by walkenbach:

http://j-walk.com/ss/books/index.htm

some reviews by Dave Hawley, fwiw:

http://www.ozgrid.com/Services/ExcelBooks.htm

 

[AdminCo]

Thanks Paddy. I'll take a peak.

Regards,

Alan