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

The issue that I have is when I add dates to a textbox in the userform

some times it does in dd/mm/yyyy and other for a extrange it is in MM/DD/yyyy then the current system date that is for example 4 of july it came as 7 of April :(

For i = 1 To DateRange

Dia2Add = DateAdd("d", i, DateIni)

MsgBox i & ": " & Dia2Add

I_Fecha.AddItem Dia2Add

Next i