Saving Dates from User Form to table

[FlashNZ]

Hi, I have a simple User Form where I want to save some data into a table in excel. The UF has a date field on it using a Date Picker.

Trouble is when the date is saved to the table it switches the date selected from 1/4/2023 (dd/mm/yyyy) to 4/1/2023 (mm/dd/yyyy) format.

How do I get it to save the date in the correct format (dd/mm/yyyy)?

Code is:

Private Sub cmdAdd_Click()

Dim Salesperson As String
Salesperson = TextBox1.Text

Dim DatefstSale As String
DatefstSale = DTPicker1


Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Active Table")

Set tbl = ws.ListObjects("Ann_BDListings")

Dim lRow As ListRow
Set lRow = tbl.ListRows.Add

With lRow
.Range(1) = Salesperson
.Range(7) = DatefstSale


End With

End Sub

 

[Mario_R]

Hello.
I would imagine that DatefstSale must be a textBox.
When you take its value to a cell you must do it like this:

.Range(7) = CDate(DatefstSale)

 

[FlashNZ]

Thanks Mario, works perfectly.

 

[Mario_R]

It was a pleasure helping you.