Help getting ComboBox to display time format

[Deekappa]

I have the code below in a userform, with 5 combo boxes. I need Combo Box 2 to display a time, but I can't get it to work. I've had a look around and tried a few suggestions, but can't seem to get it. Any suggestions would be hugely appreciated.

' Open UserForm1 when the workbook is opened
Private Sub UserForm_Initialize()


    ComboBox1.RowSource = ""
    ComboBox1.List = Worksheets("Drop Down List Source").Range("A2", Worksheets("Drop Down List Source").Range("A" & Rows.Count).End(xlUp)).Value
 
    ComboBox2.RowSource = ""
    ComboBox2.List = Worksheets("Drop Down List Source").Range("B2", Worksheets("Drop Down List Source").Range("B" & Rows.Count).End(xlUp)).Value
 
    ComboBox3.RowSource = ""
    ComboBox3.List = Worksheets("Drop Down List Source").Range("C2", Worksheets("Drop Down List Source").Range("C" & Rows.Count).End(xlUp)).Value
    
    ComboBox4.RowSource = ""
    ComboBox4.List = Worksheets("Drop Down List Source").Range("D2", Worksheets("Drop Down List Source").Range("D" & Rows.Count).End(xlUp)).Value
    
    ComboBox5.RowSource = ""
    ComboBox5.List = Worksheets("Drop Down List Source").Range("E2", Worksheets("Drop Down List Source").Range("E" & Rows.Count).End(xlUp)).Value
    
End Sub

 

[mikerickson]

Dim OneCell as Range
'...
With Worksheets("Drop Down List Source").Range("E:E")
    For Each oneCell in Range(.Cells(2,1), .Cells(Rows.Count,1).End(xlup)).Cells
        ChomboBox5.AddItem Format(oneCell.Value, "h:mm:ss")
    Next oneCell
End With

 

[Deekappa]

I tried your suggestion, but I still can't get it to work. Would it be easier if I attached a copy of the file?

 

[mikerickson]

Doesn't work isn't very specific.
What values do you have in column E? Are they strings or actual excel serial dates? (a formula like =ISNUMBER(E2) will distinguish between those)
What is shown in the combobox?

 

[Deekappa]

The source column has times from 7:00:00 AM - 5:00:00 PM, in 30 minute increments.

I added the code below and now it displays the time in the correct format, once you select an option. However, the list that appears is displaying the times as a decimal value still.

Private Sub ComboBox2_Click()
    ComboBox2.Value = Format(ComboBox2.Value, "hh:mm AM/PM")
End Sub

 

[Norie]

Deekappa

Do you want to have comboboxes which list the times 7:00:00 AM, 7:30:00 AM, ...., 5:00:00 PM or do the various columns on 'Drop Down List Source' have specific times?