I have a dreaded date issue that I could do with some assistance with please.
I have a userform which is populated with data from an autofiltered range on my spreadsheet. The user fills in the only enabled textbox on the userform which is the date a letter is sent (TxtLetterSent). Once the user closes the userform the code then updates the current row on the spreadsheet to include the date that the user has just entered into the TxtLetterSent field on the userform.
In order to sort the date into the correct format I have the following code in an AfterUpdate sub on the TxtLetterSent field:
I am then using the following code to place the date in the correct place on the spreadsheet:
The code is all working correctly and the date in the TxtLetterSent field is formatted correctly as dd/mm/yyyy but when it puts the date into my spreadsheet is changes it to the mm/dd/yyyy format. My regional settings on my computer are set to dd/mm/yyyy as is the field on the spreadsheet where the data is to be placed.
After running the line of code to place the date in the spreadsheet, if I
Any help would be greatly appreciated.
I have a userform which is populated with data from an autofiltered range on my spreadsheet. The user fills in the only enabled textbox on the userform which is the date a letter is sent (TxtLetterSent). Once the user closes the userform the code then updates the current row on the spreadsheet to include the date that the user has just entered into the TxtLetterSent field on the userform.
In order to sort the date into the correct format I have the following code in an AfterUpdate sub on the TxtLetterSent field:
VBA Code:
Private Sub TxtLetterSent_AfterUpdate()
'Checks that the date entered in the letter sent field is a date
If IsDate(Me.TxtLetterSent.Value) Then
Me.TxtLetterSent.Text = Format(Me.TxtLetterSent.Text, "DD/MM/YYYY")
Else
MsgBox ("Incorrect date entered!"), vbCritical, "GMP - VPRS"
TxtLetterSent.Value = ""
End If
End Sub
I am then using the following code to place the date in the correct place on the spreadsheet:
Code:
rngColLetterSent.Cells(r).Value = Format(.TxtLetterSent.Text, "dd/mm/yyyy")
The code is all working correctly and the date in the TxtLetterSent field is formatted correctly as dd/mm/yyyy but when it puts the date into my spreadsheet is changes it to the mm/dd/yyyy format. My regional settings on my computer are set to dd/mm/yyyy as is the field on the spreadsheet where the data is to be placed.
After running the line of code to place the date in the spreadsheet, if I
debug.print
the TxtLetterSent field I get the date showing correctly as dd/mm/yyyy but if I then debug.print
the rngColLetterSent it is showing as mm/dd/yyyy.Any help would be greatly appreciated.