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

 

[Jack George]

In the frmInvoice module, line 1649

txtStartTime201 = ref.Offset(, 7).value

should be changed to

txtStartTime201 = Format$(ref.Offset(, 7).value, "hh:mm")

For others, do the same and see if the result is as expected.

 

[gilly01625]

In the frmInvoice module, line 1649

txtStartTime201 = ref.Offset(, 7).value

should be changed to

txtStartTime201 = Format$(ref.Offset(, 7).value, "hh:mm")

For others, do the same and see if the result is as expected.

That works perfectly! Thank you so much!

 

[Jack George]

That works perfectly! Thank you so much!

You're welcome.