Put date into spreadsheet from userform textbox in correct format

K1600

Board Regular
Joined
Oct 20, 2017
Messages
181
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:

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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi,
try using DateValue function & see if this solves your problem

VBA Code:
rngColLetterSent.Cells(r).Value = DateValue(Me.TxtLetterSent.Text)

Dave
 
Upvote 0
Perfect. Star man!

Thank you kindly

Welcome glad suggestion helped.
Just be aware that if your textbox does not contain a date then the function will error

Dave
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top