Listbox Column Widths and Labels as Headers

[jstevens]

I'm having a challenge adjusting Listbox column widths to Label headers. The labels are the same width but as you can see text values are shifting.

I'm prefixing values with blank spaces with: 8 - Len(Format(sh.Cells(r, c).Value, "#,##0"))) & Format(sh.Cells(r, c).Value, "#,##0") so the values shown are a fixed length.

EL_108.png

Your thoughts are appreciated.

[HansV]

What is the Width setting of the labels?
And what is the Column Widths setting of the list box?

[jstevens]

Hans,

Label width = 67
Column width = 63 pt

[HansV]

I can reproduce the behavior. It looks like the columns of a listbox aren't spaced 100% regularly.
So I fear that you'll have to adjust the position and width of the labels manually.

Another option would be to set the Row Source of the listbox to the relevant range, and to set its TextAlign property to 3 - fmTextAlignRight so that the listbox items will be right-aligned. Make sure that the row above the source range contains the label captions, and set the ColumnHeads property of the listbox to True.

[jstevens]

Hans,

Thanks for the suggestions.

[jstevens]

Hans,

I got it to space correctly using the Courier New font in the ListBox and Labels with the same font size.

I also had to make slight width adjustments to the labels.