Put date into spreadsheet from userform textbox in correct format

K1600

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,521
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try using DateValue function & see if this solves your problem

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

Dave
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
Hi,
try using DateValue function & see if this solves your problem

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

Dave
Perfect. Star man!

Thank you kindly
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,521
Office Version
  1. 2019
Platform
  1. Windows
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
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
Welcome glad suggestion helped.
Just be aware that if your textbox does not contain a date then the function will error

Dave
Cheers,

I've built a bit in to cover if the value isn't a date too.

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,124
Messages
5,640,249
Members
417,131
Latest member
Seanr19871

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
Top