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'

 

[Jack George]

Original format from the user is 00/00/000, and it is returned as a 5 digit number (0000)

The manual input by a user can go really wild and, both Excel as an application and VBA as a language have specific valid range of dates (I can't recall what those ranges are but its easy to look into the docs and find out). Out of that range, a human might be able to recognize it as a valid date but Excel/VBA will fail to intepret it as a date.

Since you didn't provide data here for us to debug the code for you. It would be helpful if you can tell us what exactly is the user input and what the corresponded resulting 5 digit number is. Not just the format.

 

[gilly01625]

Here is my spreadsheet - Dropbox

Specifically now (after some revisions), the issue is on frmInvoice in that a cell from a table is in a time format, but is being pulled onto the userform in the format of a number with decimal place.

Open frmInvoice > Select 'FULL' from the Format Combo Box located bottom right of form > Select "OLUK-WREF-0001" from Reference Combo Box '1'

The text boxes in that row on the userform should be populated. With "OLUK-WREF-0001", the Start Time should be "10:00" (but is being displayed as "0.416666666666667"), and End time should be "15:00" (but is being displayed as "0.625").

Have the same issue with dates on frmWork - however have a workaround which seemed to resolve - you might be able to suggest a different solution? (have tried this workaround on frmInvoice for the times but did not work).

Does that make sense?

Thanks