Excel VBA Userform Date issue

Hi Ossie

Thanks for that

I guess I didnt explain to well

so on my user form  frmincident - data is inputted as per the form questions - the first question is incident data - which is in a normal text box - the user in puts the data as dd/mm/yyyy - on completion of the entries the user presses save and the data is transferred on to the worksheet - thats all fine - im using this vba

Sub Submit()

Dim sh As Worksheet

Dim irow As Long

Set sh = ThisWorkbook.Sheets("Database")

If frmincident.txtRowNumber.Value = "" Then

irow = [Counta(Database!A:A)] + 1

Else

irow = frmincident.txtRowNumber.Value

End If

With sh

.Cells(irow, 1) = "=Row()-2" 'Dynamic Serial Number

.Cells(irow, 2) = frmincident.txtcomment.Value

.Cells(irow, 3) = frmincident.cmbdelay.Value

.Cells(irow, 5) = frmincident.txtfinished.Value

.Cells(irow, 7) = frmincident.txtarrival.Value

.Cells(irow, 9) = frmincident.txtturnout.Value

.Cells(irow, 11) = frmincident.txtdispatch.Value

.Cells(irow, 12) = frmincident.cmbproperty.Value

.Cells(irow, 13) = frmincident.cmbincident.Value

.Cells(irow, 14) = frmincident.cmbcommunity.Value

.Cells(irow, 15) = frmincident.cmbstation.Value

.Cells(irow, 16) = frmincident.cmbcall.Value

.Cells(irow, 17) = frmincident.txttime.Value

.Cells(irow, 18) = frmincident.txtdate.Value

.Cells(irow, 19) = frmincident.txtautomax.Value

.Cells(irow, 20) = Application.UserName

.Cells(irow, 21) = [Text(Now(),"DD-MM-YYYY HH:MM:SS")]

End With

End Sub

That works and the Date format is correct at that point

Then also within a list box on the user form the inputted data is also visualised as per

      .LstDatabase.ColumnCount = 21

.LstDatabase.ColumnHeads = True

.LstDatabase.ColumnWidths = "20,80,120,92,100,90,95,120,90,110,120,90,85,90,90,70,60,70,110,80,80,80,80"

If irow > 1 Then

.LstDatabase.RowSource = "Database!A2:T" & irow

Else

.LstDatabase.RowSource = "Database!A2:T2"

End If

End With

End Sub

Now this is when the issue occurs - if you select any of the entries from the list box for editing by selecting the list box entry and pressing a cmd button 

and this runs

Private Sub cmdedit_Click()

If Selected_List = 0 Then

MsgBox "No Row is Selected.", vbOKOnly + vbInformation, "Edit"

Exit Sub

End If

'Code to update the value to respective controls

Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 0), _

ThisWorkbook.Sheets("Database").Range("A:A"), 0) 'Selected_List + 1

Me.txtcomment.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 1)

Me.cmbdelay.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 2)

Me.txtfinished.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 4)

Me.txtarrival.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 6)

Me.txtturnout.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 8)

Me.txtdispatch.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 10)

Me.cmbproperty.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 11)

Me.cmbincident.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 12)

Me.cmbcommunity.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 13)

Me.cmbstation.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 14)

Me.cmbcall.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 15)

Me.txttime.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16)

Me.txtdate.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 17)

Me.txtautomax.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 18)

MsgBox "Please make the required changes and click 'Save' button to update.", vbOKOnly + vbInformation, "Edit"

End Sub

so this is when the problem occurrs so if there is a date such 06/12/2020 the entry ti the txtdate box comes back as number eg 09/01/2020 now shows as 43839

Can you offer any further guidance please ?

I have a further problem which is also similar with time - if the excel works sheet is not formatted as a text cell the value returns - as a decimal  - so if the cells are formated as 'TIME' hh:mm it will not return a time format from the user form list box into the relevant txtboxes

Thank you

I wonder what code you are using to populate the relevant TextBox or Worksheet Cell.

The below code example loops through dates in column A and inserts the date in the correct Regional format in the TextBox and then copies the TextBox date back to the to the adjacent column B cell on the worksheet and sets the number format for column B. (You can pre set the number format for the entire column B so that the number format is not required for each loop).

You may not need to do this but ensure that you have the correct Regional Settings for the dates in windows 10. See the following link for more information on how to do this.

https://support.office.com/en-us/article/change-the-windows-regional-settings-to-modify-the-appearance-of-some-data-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

Private Sub CommandButton1_Click()

    Dim r As Long

    For r = 2 To 32

        'Populate a Userform TextBox from a date cell on worksheet

        Me.TextBox1 = Format(Worksheets("Sheet1").Cells(r, "A"), "dd/mm/yyyy")

        'Populate a worksheet Date Cell from a Userform TextBox date

        Worksheets("Sheet1").Cells(r, "B") = DateValue(Me.TextBox1.Value)

        Worksheets("Sheet1").Cells(r, "B").NumberFormat = "dd/mm/yyyy"

    Next r

End Sub