VBA Userform Date Format

[gilly01625]

Hey,

I have a userform which populates a table on a worksheet with data inputted by the user from a range of text boxes and combo boxes. I then have a 'recall' feature which pulls the data back into the userform, allowing the data to be changed and manipulated before being updated back into the worksheet.

I have x2 text boxes for dates to be inputted into, however when I 'recall' the data back into the form, the dates get converted into a number format. Original format from the user is 00/00/000, and it is returned as a 5 digit number (0000). How do I convert this number into the original date inputted by the user?

I use the below code to pull the data from a List Box, and display back into the original text boxes - they come from txtDateStart, and txtDateEnd.


Thanks

'===================================================================================================
'Pull Table Contents to Userform Input Options
'===================================================================================================

Private Sub lstWorkDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Call Date_Format
    
    Me.txtWID.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 0)
    Me.txtWREF.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 1)
    Me.cmbClient.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 2)
    Me.txtSubClient.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 3)
    Me.cmbType.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 4)
    Me.txtLocation.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 5)
    Me.txtDateStart.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 6)
    Me.txtDateEnd.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 7)
    Me.txtS1Start.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 8)
    Me.txtS1End.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 9)
    Me.txtS2Start.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 10)
    Me.txtS2End.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 11)
    Me.txtS3Start.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 12)
    Me.txtS3End.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 13)
    Me.txtQuotedHours.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 14)
    Me.txtActualHours.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 15)
    Me.txtMileage.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 16)
    Me.txtPetrol.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 17)
    Me.txtParking.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 18)
    Me.txtHourly.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 19)
    Me.txtDay.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 20)
    Me.txtSalary.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 21)
    Me.txtTotal.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 22)
    Me.txtIID.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 23)
    Me.cmbPAYE.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 24)
    Me.txtNotes.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 25)
    Me.cmbCountry.Value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 27)
    
End Sub

 

[gilly01625]

Have resolved this using below code

Me.txtDateEnd.value = Format(Format(txtDateEnd, "Long Date"), "DD/MM/YYYY")

 

[gilly01625]

Not resolved anymore - can anyone help?

 

[Alex Blakenburg]

What happens if you just use this part:

Me.txtDateEnd.value = Format(txtDateEnd, "DD/MM/YYYY")

 

[gilly01625]

Sti

What happens if you just use this part:

Me.txtDateEnd.value = Format(txtDateEnd, "DD/MM/YYYY")

Still get the date returned in numbers

 

[Alex Blakenburg]

What happens if you use this:


Me.txtDateEnd.value = Format(CDate(txtDateEnd), "DD/MM/YYYY")

 

[Alex Blakenburg]

What do you get in the immediate window if you put this before the above line ?

Debug.Print txtDateEnd

 

[gilly01625]

What do you get in the immediate window if you put this before the above line ?

Debug.Print txtDateEnd

I get 'Run Time Error 13, Type Mismatch'