Excel vba error with dates on sheet and textbox

Question

I live in Australia and we use the d/mm/yyyy date format. I am trying to make a userform with VBA in excel that will read the a cell (A1), display it in a textbox. The user can then enter a date in another textbox and set that date back to cell(A1). The problem I have is that when I am reading the date from Cell(A1) is changes from d/mm/yyyy to m/dd/yyyy in the textbox. I have used the " = Format(DateCurrent_Tbx.Value, "d/mmm/yy")" but it makes no difference. I am using Excel 2010 32bit, on a Windows 7 SP1 64Bit Computer, and have the computer set to English (Australian) time format. Cell(A1) is set to custom formatting "d/mm/yyyy"

As the VBA code for the file I'm using is very long, I've replicated the error in a smaller userform (attached excel file).

Sample of Excel file with Error

Private Sub LockInput_Cmd_Click()
SetDate_Cmd.Caption = InputDate_Tbx.Value
SetDate_Cmd.Caption = Format(SetDate_Cmd.Caption, "d/mmm/yy")
End Sub

Private Sub SetDate_Cmd_Click()
ThisWorkbook.Sheets(1).Range("A1").Value = SetDate_Cmd.Caption
DateCurrent_Tbx.Value = ThisWorkbook.Sheets(1).Range("A1").Value
DateCurrent_Tbx.Value = Format(DateCurrent_Tbx.Value, "d/mmm/yy")

End Sub

Private Sub UserForm_Initialize()
DateCurrent_Tbx.Value = ThisWorkbook.Sheets(1).Range("A1").Value
DateCurrent_Tbx.Value = Format(DateCurrent_Tbx.Value, "d/mmm/yy")
End Sub

I have been racking my head and searching the web for days to no avail. I hope I have explained things clearly enough.

Unknown Unknown · Accepted Answer · 2015-08-09T11:59:45

There is some confusion with strings that look like dates and actual dates and the EN-US-centric VBA default MDY locale is doing nothing to alleviate that.

First off, the date in A1 is a number between 0 and 42,225 (Aug 9, 2015 is 42,225); nothing more or less. You can dress it up as d/mm/yyyy regardless of what locale your computer system is running under. Mine runs under EN-US and I have no trouble displaying a date as 9/08/2015 if I use a number format mask of d/mm/yyyy. To get the displayed date from A1 back into the textbox AS A STRING use the Range.Text property. This is the displayed text that is in a cell. It is read-only so you cannot stuff a "9/08/2015" string back into it.

Private Sub UserForm_Initialize()
    DateCurrent_Tbx.Value = ThisWorkbook.Sheets(1).Range("A1").TEXT
End Sub

As far as user input of a 'string-that-looks-like-a-date' is concerned, you may have to trust your users a bit to do the right thing. If they can be relied upon to input in a DMY format, you can split the pieces and put them together properly using the DateSerial function.

Private Sub LockInput_Cmd_Click()
    dim dt as date, vDT as variant
    vDT = split(InputDate_Tbx.Value, chr(47))  'split the string-date on the forward slash
    dt = DateSerial(vDT(2), vDT(1), vDT(0))   'create a real date
    SetDate_Cmd.Caption = Format(dt, "d/mmm/yy")   'use any format mask you want to create another string-date
End Sub

Private Sub SetDate_Cmd_Click()
    dim dt as date, vDT as variant
    vDT = split(SetDate_Cmd.Caption, chr(47))  'split the string-date on the forward slash
    dt = DateSerial(vDT(2), vDT(1), vDT(0))   'create a real date
    ThisWorkbook.Sheets(1).Range("A1").Value = dt  'put the actual date back into A1
End Sub

As far as the CDate function is concerned, I would avoid its use. If you stuff "19/08/2015" into CDate (try it in the VBE's Immediate window as ?CDate("19/08/2015")) it will correctly interpret it as 19-Aug-2015 but that is only because IT HAS NO OTHER CHOICE! If you try the same with ?CDate("9/08/2015") you end up with 08-Sep-2015 because whenever there is a choice, CDate opts for the EN-US locale of MDY. Best not to rely on this one. I believe the DateValue function is at least as unreliable.

Excel vba error with dates on sheet and textbox

1 Answers