Format Pulled Data from Workbook into a Text Box

[gilly01625]

Hi,

I have a userform which allows the user to fill a range of text boxes and combo boxes with information, and once an 'Add' button is pressed, the information is stored into a table on a worksheet. The user can then 'retrieve' the same information back into the userform by double clicking on a row within a List Box which is displaying the table information.

I have x2 text boxes which require the user to input a start time, and an end time in the format of HH:MM. This format is stored in the table, however when the data is 'retrieved', it is converted into a number with many decimal places. For example, user inputs '10:00', but when 'retrieved', the text boxes displays '0.416666666666667'.

Any idea why and how I go about fixing this?

Below is the code from the 'retrieval' of information.

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

Private Sub lstWorkDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    Me.txtDateStart = Format("DD/MM/YYYY")
    Me.txtDateEnd = Format("DD/MM/YYYY")
    
    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 = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 6)
    Me.txtDateEnd.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 7)
    Me.txtS1Start.Text = 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.cmbTransportType.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 16)
    Me.txtTransportTotal.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 17)
    Me.txtMileage.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 18)
    Me.txtPetrol.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 19)
    Me.txtParking.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 20)
    Me.txtHourly.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 21)
    Me.txtDay.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 22)
    Me.txtSalary.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 23)
    Me.txtTotal.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 24)
    Me.cmbIID.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 25)
    Me.cmbPSID.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 26)
    Me.txtNotes.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 27)
    Me.cmbCountry.value = Me.lstWorkDatabase.List(Me.lstWorkDatabase.ListIndex, 29)
    
End Sub

 

[gilly01625]

I have added a 'Format' for these Start and End Time text boxes, but still does not change the format.

I had a similar issue with dated being pulled back into the userform in the 1900 date format (5 digits) - I got around this by adding a 'Format' code to a Userform Mouse Move Sub. Couldn't find another way around this either

 

[gilly01625]

This is the Mouse Move Code - is there a way to format text boxes within using this code?

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    
    Me.txtDateEnd = Format(txtDateEnd, "DD/MM/YYYY")
    Me.txtDateStart = Format(txtDateEnd, "DD/MM/YYYY")
    
    Me.txtS1Start = Format(txtS1Start, "HH:SS")
    Me.txtS1End = Format(txtS1End, "HH:SS")
    Me.txtS2Start = Format(txtS1Start, "HH:SS")
    Me.txtS2End = Format(txtS1End, "HH:SS")
    Me.txtS3Start = Format(txtS1Start, "HH:SS")
    Me.txtS3End = Format(txtS1End, "HH:SS")
    
End Sub