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

 

[Skyybot]

Whenever I pull data from a Worksheet, I usually declare the data type. Helps protect against Format issues. For the following examples I have 11:00:00 AM in Cell "A1" and it's formatted as Time (defaualt time format).

This sub gives me 11:00:00 AM

Option Explicit

Sub GetData()
Dim d As Date
d = Me.Cells(1, 1).Value
Debug.Print d
End Sub

This sub gives me 0.458333333333333

Option Explicit

Sub GetData()
Dim d
d = Me.Cells(1, 1).Value
Debug.Print d
End Sub

 

[gilly01625]

Whenever I pull data from a Worksheet, I usually declare the data type. Helps protect against Format issues. For the following examples I have 11:00:00 AM in Cell "A1" and it's formatted as Time (defaualt time format).

This sub gives me 11:00:00 AM

Option Explicit

Sub GetData()
Dim d As Date
d = Me.Cells(1, 1).Value
Debug.Print d
End Sub

This sub gives me 0.458333333333333

Option Explicit

Sub GetData()
Dim d
d = Me.Cells(1, 1).Value
Debug.Print d
End Sub

Sorry, how would I integrate this into my code printing the value into the text box?
Thanks

 

[Skyybot]

Option Explicit

Sub GetData()
Dim d As Date
d = Sheet1.Cells(1, 1).Value
UserForm1.TextBox1.Text = CStr(d)
Debug.Print d
End Sub

 

[Fuji]

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.416666666666

Data in controls are String type.
When transfer to the cell, Excel converts to appropriate data type (Auto casting).

So, if you are impoting back to the control(s), it needs to be converted to appropriate format.

Easiest way is to read the .Text property from the cell, e,g

TextBox1 = Range("A1").Text

Or something like

TextBox1 = Format(Range("a1"),"HH:MM")