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

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
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! :)

Code:
        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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,338
Try:

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

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
Awesome Eric, it worked perfectly. Thank you!

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
You can only have column headers if you load the listbox by Rowsource.
 

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
I had a feeling. Okay, thanks Fluff. Not a huge loss if I don't have it showing.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,482
Messages
5,414,797
Members
403,543
Latest member
rabdorawsh

This Week's Hot Topics

Top