CODEWITHSUNDEEP
vbaexceluserformdate-formatting
Diego Ali
Diego Ali

Reputation: 27

VBA: Userform Error date values change - months as days and days as months

I'm using this calendar in my userform to enter dates in Textbox1. It works fine within the userform (format "dd/mm/yyyy") - no problem either filling dates directly to an active cell.

The problem comes when passing the data from the userform to the worksheet. The date format stays "dd/mm/yyyy" but Changing the day value as month and month as day ( March 1 becomes january 3).

ws.Cells(MyNR, 1) = tbFi.Value
ws.Cells(MyNR, 2) = tbNh.Value
ws.Cells(MyNR, 3) = tbTh.Value
ws.Cells(MyNR, 4) = cbN1.Value
ws.Cells(MyNR, 5) = cbPoc.Value
ws.Cells(MyNR, 6) = cbNa1.Value
ws.Cells(MyNR, 7) = cbPoca1.Value
ws.Cells(MyNR, 8) = cbNa2.Value
ws.Cells(MyNR, 9) = cbPoca2.Value
ws.Cells(MyNR, 10) = cbNa3.Value
ws.Cells(MyNR, 11) = cbPoca3.Value
ws.Cells(MyNR, 12) = cbNa4.Value
ws.Cells(MyNR, 13) = cbPoca4.Value
ws.Cells(MyNR, 14) = TextBox1.value
   'ws.Cells(mynr, 14).NumberFormat = "dd/mm/yyyy" not working
ws.Cells(MyNR, 15) = TextBox2.value
    'ws.Cells(mynr, 15).NumberFormat = "dd/mm/yyyy"
ws.Cells(MyNR, 16) = cbQac.Value

Upvotes: 1

Views: 1086

Answers (1)

Rory
Rory

Reputation: 34035

Use CDate to convert the text to a real date value:

ws.Cells(MyNR, 14).Value = CDate(TextBox1.value)
ws.Cells(MyNR, 15).Value = CDate(TextBox2.value)

Upvotes: 1

Related Questions