my userform is automatically changing the date format

Question

my userform to sign out equipment is automatically changing the date to US format (e.g. mm/dd/yyyy), even though it is being completed using UK format date (i.e. dd/mm/yyyy). Is there something I could change or add a line into the code to stop this from happening?

Private Sub CommandButton1_Click()
Dim A As Long
Sheets("Inventory").Select
Columns("A:E").Select
ActiveSheet.Unprotect
Sheets("MainPage").Select

A = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row + 1
    Sheet2.Cells(A, 1).Value = ComboBox1.Value
    Sheet2.Cells(A, 2).Value = Clienterva.Value
    Sheet2.Cells(A, 3).Value = Physerva.Value
    Sheet2.Cells(A, 4).Value = Dateerva.Value
    ComboBox1.Value = ""
    Patienterva.Value = ""
    Physerva.Value = ""
    Dateerva.Value = ""


Sheets("Inventory").Select
Columns("A:E").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("MainPage").Select
End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub Noxerva_Change()
Me.Noxerva = UCase(Me.Noxerva)
End Sub


Private Sub Clienterva_Change()
Me.Patienterva = UCase(Me.Patienterva)
End Sub

Private Sub Physerva_Change()
Me.Physerva = UCase(Me.Physerva)
End Sub

Thank you in advance

Have you tried setting the date format you want to the cell where you write it? This may be useful. Side note: in general, you want to avoid using select in your code — cybernetic.nomad
– cybernetic.nomad, Commented May 13 at 17:49
Assuming your regional settings are UK, use CDate - i.e. Sheet2.Cells(A, 4).Value = CDate(Dateerva.Value) — Rory
– Rory, Commented May 15 at 10:15

VBasic2008 · Accepted Answer · 2025-05-13 19:09:30Z

1

Convert String to Date

In your case, where e.g. today's date is the string "13/05/2025", you could use the following function to convert this string to a (VBA) date:

Function StringToDate(ByVal DateString As String) As Date
    Dim Arr() As String: Arr = Split(DateString, "/")
    StringToDate = DateSerial(Arr(2), Arr(1), Arr(0))
End Function

You could utilize it in the following way:

Sheet2.Cells(A, 4).Value = StringToDate(Dateerva.Value)

You could test the function with something like the following:

Sub Test()
    Const DateString As String = "13/05/2025"
    Debug.Print StringToDate(DateString) ' resulting US date: '5/13/2025'
End Sub

answered May 13 at 19:09

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tinazmu · Accepted Answer · 2025-05-13 22:47:04Z

0

You can use DateValue(): like:

Sheet2.Cells(A, 4).Value = DateValue(Dateerva.Value)

DateValue honours the regional settings, unless the input is wrong for that setting. i.e. if your regional date format is dd/mm/yyyy, and you provide 05/14/2025, then it tries to interpret this as 14-May-2025. It is best to check the input value before relying on it as a as a date.

answered May 13 at 22:47

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Collectives™ on Stack Overflow

my userform is automatically changing the date format

2 Answers 2

Convert String to Date

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Convert String to Date

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related