Listbox Time Formatting

[dunnerca]

I have created a listbox using the following code:

With ThisWorkbook.Sheets("inventory")
vList = .Range("a2", .Range("a2").End(xlDown).Offset(0, 3))
End With
Me.lst_inventory.ListStyle = fmListStyleOption
frm_inventory.lst_inventory.List = vList

The last two columns are start and end times, formatted as HH:MM AM/PM on
the worksheet.

When brought into the Listbox, the times are shown as their serial numbers
(ie .2500). Is there a way to format the appearance of the time fields in
the Listbox using the method of creating the listbox as shown above?

Thanks.

 

[Jim Rech]

Your code uses the Value property of each cell by default since you didn't
specify one. You have to use the Text property. I think you have to go
through the cells individually to do that:

Private Sub UserForm_Initialize()
Dim rgList As Range
Dim Cell As Range
With ThisWorkbook.Sheets("inventory")
Set rgList = .Range("a2", .Range("a2").End(xlDown)).Offset(0, 3)
End With
lst_inventory.ListStyle = fmListStyleOption
For Each Cell In rgList
lst_inventory.AddItem Cell.Text
Next
End Sub


--
Jim
|I have created a listbox using the following code:
|
| With ThisWorkbook.Sheets("inventory")
| vList = .Range("a2", .Range("a2").End(xlDown).Offset(0, 3))
| End With
| Me.lst_inventory.ListStyle = fmListStyleOption
| frm_inventory.lst_inventory.List = vList
|
| The last two columns are start and end times, formatted as HH:MM AM/PM on
| the worksheet.
|
| When brought into the Listbox, the times are shown as their serial numbers
| (ie .2500). Is there a way to format the appearance of the time fields in
| the Listbox using the method of creating the listbox as shown above?
|
| Thanks.