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
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")
3 additional answers
Sort by: Most helpful
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.
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.
What is the solution for date format in the excel VBA form?
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!
@Azim Azrul ,
Welcome to Q&A forum!
Due to the tag office-excel-itpro focuses on general issues about Excel client, I will remove it and add the related-tag office-vba-dev.
Thanks for your understanding.
Sign in to comment