Listbox w/Day of the Week & Start/Finish Times Column Headers, and HH:MM format

[TAPS_MikeDion]

Hey guys,

My assumption is that there has to be a better way.

In my ListBox, I'm trying to get 3 column headers (DAY | START | FINISH) and also have the time displayed in the list as 10:00AM, as opposed to being a decimal (0.41666666), which is why I was trying "* 24" in the code.

If anybody can help with this, then I say...THANK YOU VERY MUCH!

        SelectedRow = Me.ComboBox1.ListIndex + 2       

        With Me.HoursSchedule 'Listbox
            .Clear
            .ColumnHeads = True
            .ColumnCount = 3
            .ColumnWidths = "96;94;94"
            .Font.Size = 14
            .AddItem "Sunday"
            .List(0, 1) = ws.Cells(SelectedRow, 40).Value * 24 'Sunday Start Time
            .List(0, 2) = ws.Cells(SelectedRow, 41).Value * 24 'Sunday Finish Time
            .AddItem "Monday"
            .List(1, 1) = ws.Cells(SelectedRow, 42).Value * 24 'Monday Start Time
            .List(1, 2) = ws.Cells(SelectedRow, 43).Value * 24 'Monday Finish Time
            .AddItem "Tuesday"
            .List(2, 1) = ws.Cells(SelectedRow, 44).Value * 24 'Tuesday Start Time
            .List(2, 2) = ws.Cells(SelectedRow, 45).Value * 24 'Tuesday Finish Time
            .AddItem "Wednesday"
            .List(3, 1) = ws.Cells(SelectedRow, 46).Value * 24 'Wednesday Start Time
            .List(3, 2) = ws.Cells(SelectedRow, 47).Value * 24 'Wednesday Finish Time
            .AddItem "Thursday"
            .List(4, 1) = ws.Cells(SelectedRow, 48).Value * 24 'Thursday Start Time
            .List(4, 2) = ws.Cells(SelectedRow, 49).Value * 24 'Thursday Finish Time
            .AddItem "Friday"
            .List(5, 1) = ws.Cells(SelectedRow, 50).Value * 24 'Friday Start Time
            .List(5, 2) = ws.Cells(SelectedRow, 51).Value * 24 'Friday Finish Time
            .AddItem "Saturday"
            .List(6, 1) = ws.Cells(SelectedRow, 52).Value * 24 'Saturday Start Time
            .List(6, 2) = ws.Cells(SelectedRow, 53).Value * 24 'Saturday Finish Time
        End With

As always, any help is greatly appreciated.

 

[Eric W]

Try:

.List(0, 1) = Format(ws.Cells(SelectedRow, 40).Value, "h:mm AM/PM") 'Sunday Start Time

 

[TAPS_MikeDion]

Awesome Eric, it worked perfectly. Thank you!

Would you happen to know how to manually enter the column headers in a Listbox?

 

[Fluff]

You can only have column headers if you load the listbox by Rowsource.

 

[TAPS_MikeDion]

I had a feeling. Okay, thanks Fluff. Not a huge loss if I don't have it showing.

 

[Fluff]

You're welcome & thanks for the feedback