Updating the date and time fields of Excel user form when form saved

My Excel user form has a date and time field that auto-populates when the form is opened. This is to reduce the overhead for the user, and to ensure the format is entered correctly. Once the form is saved, the data from the form populates a spreadsheet called 'TrackingSheet'.

I am finding that users have the form open on their computer over the course of the day, or possibly a couple of days, and as a result the date and time being recorded when the form is saved isn't accurate. I would prefer that users continue to keep the form open on their systems, to facilitate data entry. Is there a way to have clicking of the 'Save' button of the user form also update the date and time fields simultaneously, thus ensuring the accuracy of the date/time stamp in the TrackingSheet?

I have attached the user form in question.

Who is Participating?
Martin LissOlder than dirtCommented:
Change the formatting of column "D" to d-mmm-yy and replace your sub with this.

Private Sub cmdSave_Click()
Dim NextRow As Long

    With Me

        If .cboIDList.Value = "" _
           Or .cboTask.Value = "" _
           Or .txtPA_Unit.Value = "" _
           Or .txtDate = "" _
           Or .txtTime = "" _
           Or .cboInitials = "" _
           Or txtMinutes = "" _
           Or cboStatus = "" Then

            MsgBox "Cannot save as form is not complete yet", vbExclamation, "Incomplete"
            Exit Sub
        End If

        NextRow = Sheets("TrackingSheet").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheets("TrackingSheet").Range("A" & NextRow).Value = .cboIDList.Value
        Sheets("TrackingSheet").Range("B" & NextRow).Value = lblTaskText.Caption
        Sheets("TrackingSheet").Range("C" & NextRow).Value = .txtPA_Unit.Value
        Sheets("TrackingSheet").Range("D" & NextRow).Value = Format(Date, "d-mmm-yy")  
        Sheets("TrackingSheet").Range("E" & NextRow).Value = Format(TimeValue(Now), "hh:mm AM/PM") 
        Sheets("TrackingSheet").Range("F" & NextRow).Value = .txtMinutes.Value
        Sheets("TrackingSheet").Range("G" & NextRow).Value = .cboInitials.Value
        Sheets("TrackingSheet").Range("H" & NextRow).Value = .cboStatus.Value
        .cboIDList.Value = ""
        .cboTask.Value = ""
        .txtPA_Unit.Value = ""
        .txtMinutes.Value = ""
    End With

End Sub

Open in new window

AndreamaryAuthor Commented:
Hi Martin,

Excellent...thank you!! I was comparing the old and new code to try and understand what you did...if you had a moment, would you be able to explain it to me?

Thanks for the quick turnaround too!
Martin LissOlder than dirtCommented:
OK, I changed lines 23 and 24.

For line 23 Date is a built in function that in my local returns a date like 12/10/2017 . The Format function changes that so that it shows up in d (day number), mmm (abbreviated name of month), yy (two-digit tear) format with dashes in-between. You can find a lot more information about Format on the web.

For line 24 TimeValue is a built in function that given in input parameter Now (which is date and time) returns a string like 6:08:57 PM . Again Format changes that and you should be able to find the explanation at the same time that you look up Format on the web.

In any case you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
AndreamaryAuthor Commented:
Thanks, Martin!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.