CODEWITHSUNDEEP
excelvbadatetextboxuserform
Welshdpm
Welshdpm

Reputation: 49

Userform text Box - UK Date Format

Evening

I Have a userform with some text boxes that are dates. In the spreadsheet the date format is the UK date format (dd/mm/yyyy), When the textbox calls the information from the spreadsheet it converts it to International date format (mm/dd/yyyy).

Everything I'm trying is failing, any help would be greatly received.

Many thanks in advance.

Upvotes: 0

Views: 1783

Answers (1)

Leo Orientis
Leo Orientis

Reputation: 1061

Likely the text box is formatting the date with American mm/dd/yyyy style because that's the format defined in your computer's regional settings. One simple way to change this would be to change your regional setting for Short Date format.

However, you may want to share this UserForm with others. And there's no guarantee that their regional settings would be configured in the same way.

So if your form should always use UK format, regardless of the user's settings, then you could write a formatting function like this:

Public Function FormatDate(TheValue As Date) As String

    Dim dateSeparator As String
    dateSeparator = "/"

    FormatDate = Format(Day(TheValue), "00") & dateSeparator & _
        Format(Month(TheValue), "00") & dateSeparator & _
        Year(TheValue)

End Function

And use it something like this:

MyUserForm.MyTextBox.Value = FormatDate(inputDateValue)

If you need to parse this format back into a date, you can create a function like this:

Public Function UKTextToDate(DateText As String) As Date

    Dim yearPart, monthPart, dayPart As String

    ' Remember that in VBA, String indexes are 1-based, not 0-based!
    ' UK format: dd/mm/yyyy - year starts at position 7 and runs for 4 characters
    yearPart = Mid(DateText, 7, 4)
    ' month starts at position 4 and runs for 2 characters
    monthPart = Mid(DateText, 4, 2)
    ' day starts at position 1 and runs for 2 characters
    dayPart = Left(DateText, 2)

    ' We should convert the text values back to integers, in order to use them
    ' in the DateSerial function.
    UKTextToDate = DateSerial(CInt(yearPart), CInt(monthPart), CInt(dayPart))

End Function

However, beware that the above does not check for invalid input. If users can just type in anything, you'll need to write some code to guard against that.

Hope this helps!

Upvotes: 1

Related Questions