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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.