What is the solution for date format in the excel VBA form?

Question

Im still new with the excel VBA. When I key in the date in the VBA form using this format (dd/mm/yyyy), it will interpret automatically as mm/dd/yyyy in the worksheet. My computer system settings for the date format is in dd/mm/yyyy and the excel date format is dd/mm/yyyy (I tried to put it as General and custom, both didnt work). Is there any solution for this? The VBA coding might help I guess.

Thanks in advance for your help guys!

Answer 1

I am assuming that by VBA form you mean a Userform.

TextBoxes on Userforms are just that. They accept entries as Text. Therefore when getting the text, it needs to be converted to a date in the required format and then written to the worksheet.

Vice versa. In getting a date from the worksheet and writing to a Userform text box then convert the date to text.

The following example gets the entered date in a text box and converts it to a date value as a serial number.
The split function separates the text date into day, month and year and saves the values in a zero based array (with 3 elements) as follows.
arrSplit(0) is the Day
arrSplit(1) is the Month
arrSplit(2) is the Year

DateSerial function converts the separate values to a serial date and assigns the value to a date variable.

The date variable can then be written to the worksheet with the destination number format set as required.

Write the date from the TextBox to the worksheet

Private Sub cmdCopyDate_Click()
Dim arrSplit As Variant
Dim dte As Date
Dim ws As Worksheet
Dim rngDate As Range

Set ws = Worksheets("Sheet1")
arrSplit = Split(Me.txtDate, "/")
dte = DateSerial(arrSplit(2), arrSplit(1), arrSplit(0))
With ws
    Set rngDate = .Cells(2, "A")    'Alternatively .Range("A2")
End With
rngDate.NumberFormat = "dd/mm/yyyy"
rngDate.Value = dte

End Sub

Get the Date from the worksheet and write it to a TextBox on the Userform

Private Sub cmdGetDate_Click()

Dim ws As Worksheet
Dim rngDate As Range

Set ws = Worksheets("Sheet1")
With ws
    Set rngDate = .Cells(2, "A")    'Alternatively .Range("A2")
End With

Me.txtDate = Format(rngDate.Value, "dd/mm/yyyy")

End Sub

Answer 2

My apologies for the late reply but I did not get an email notification of your comment. I wonder with this forum if emails are sent if a comment is appended or only when "Reply" is clicked and then write the reply.

The first example copies the date from the Userform to a worksheet cell where Me.txtDate is the TextBox control on the Userform and .cells(2,"A") or .Range("A2") is the cell on the worksheet.

The second example does the opposite. It copies a date from the worksheet to a Userform. I included this example because often it is necessary to get a date from the Worksheet and display that date on the Userform.

Answer 3

Personally, I think that it is always good practice to format the data in the required format; particularly dates.

The OP for this thread was having difficulties with Excel interpretting the dates that were entered in the Userform TextBox and I provided a method of splitting the date into its' Day, Month and Year and creating a date from that information.

There have been problems with dates in m/d/y and d/m/y format for a very long time so it is best to specify what is required in each circumstance.

Answer 4

@Carlos Soares ,

Your comment "Having an office in English but configured with the language in Portugal like mine, can it interfere with anything? ". Everything in my computer is in English therefore I can't test and cannot help further with your problem.

I suggest that you start a new thread and ensure that you include exactly what is not working, including the language and maybe screen shots if possible and someone else might be able to assist.

It is never a good idea to append your question to other threads. Always best to start a new thread and if similar to another thread then include a link to it.

Answer 5

I tried your code from Userform Textbox to a workbook cell but dd/mm is swtiched to mm/dd.

I display it in the userform macro and date is ok but somehow workbook cell switch it.

can you help with this,

'Split Fecha

FechaSplit = Split(Me.I_Fecha, "/")

FechaOk = DateSerial(FechaSplit(2), FechaSplit(1), FechaSplit(0))       ‘ Get date from Year, Month & Day

FechaFull = FechaOk & " " & I_Hora.Value                                           ‘ Date and Time

MsgBox FechaOk & vbNewLine & Format(FechaOk, "dd/mmm/yyyy")

MsgBox FechaFull & vbNewLine & Format(FechaFull, "dd/mmm/yyyy hh:mm")

IncSh.Range(UF_FechaFull & UltimoReng).NumberFormat = "dd/mmm/yy" & Chr(10) & "hh:mm"

IncSh.Range(UF_FechaFull & UltimoReng).Value = FechaFull