Excel VBA Userform Date issue

Anonymous
2020-05-12T23:24:57+00:00

I am creating an excel database populated by a userform - the userform can also be re-populated with the data from the spreadsheet for later editing, deletion printing etc.Te issue is that the textbox populates the relevant cells in the correct dd/mm/yyyy UK regional format - however if the data contains a leading xero eg-02/08/2020 the value on the user form is returned as a numerical string - any other dates with a leading number of 1,2 or 3 such 21/09/2020 works fine The spreadsheet cells are all formatted correctly with a data format as above - Ive tried everything I can find and think of - can you advice pleaseABrown

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

62 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-07T15:55:32+00:00

    Hi Ozzie

    That worked fine - thank you so much for everything

    Adrian

    Was this answer helpful?

    0 comments
  2. OssieMac 48,006 Reputation points Volunteer Moderator
    2020-06-06T12:04:52+00:00

    Hi Adrian,

    Couple of questions.

    Do you want the Close Date and Time Stamp on a separate worksheet or included somewhere on the main data sheet?

    Do you want a full log of each time the workbook is closed or just the last time it was closed at a single location and updated each time the workbook is closed?

    The code example below produces a log of the close date and time. It goes in ThisWorkbook module. Note that after the code enters the date and time it will request the user to indicate whether to Save the workbook before closing because a change is made with the Date and Time.

    The Date and time log looks like the following screen shot. I have formatted the date using the alpha abbreviation. I like this method because it leaves no doubt with the day and month.

    Copy the following code and paste into ThisWorkbook module. If the worksheet does not currently exist then the code will automatically add and name the log sheet.

    Do not change the sub name because it is event code that runs automatically when the workbook is closed.

    If not what you want then please get back to me with more explanation.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

        Dim strSaveLog As String

        Dim wsSaveLog As Worksheet

        Dim rngDateTime As Range

        'Next Line edit "Date And Time" to preferred sheet name to save the Date and Time Stamp

        strSaveLog = "Date And Time"

        On Error Resume Next

        Set wsSaveLog = Worksheets(strSaveLog)

        On Error GoTo 0

        If wsSaveLog Is Nothing Then

            'If Date and Time log sheet not present then add the sheet.

            Set wsSaveLog = Worksheets.Add(After:=Worksheets(Sheets.Count))

            wsSaveLog.Name = strSaveLog

            With wsSaveLog

                .Range("A1") = "Date"

                .Range("B1") = "Time"

                .Range("A1:B1").Font.Bold = True

            End With

        End If

        With wsSaveLog

            'Find the next blank row in column A

            Set rngDateTime = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)

        End With

        rngDateTime.Value = Date

        rngDateTime.Offset(0, 1).Value = Time

        rngDateTime.NumberFormat = "dd mmm yyyy"  'Edit "dd mmm yyyy" to preferred date format.

        rngDateTime.Offset(0, 1).NumberFormat = "h:mm:ss AM/PM"

        wsSaveLog.Columns("A:B").AutoFit

    End Sub

    Was this answer helpful?

    0 comments