Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Listbox AutoSize > How It Works
Deze pagina in het Nederlands

Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox

How The Resize Works

Various people have devised a trick to calculate the column widths. Some use constants, which which the number of characters to be displayed is multiplied. This trick doesn't work reliably since screen resolution and Font may affect this.
The best method I know of is by using a (hidden) label with its AutoSize property set to True. This label must have the same Font characteristics as the ListBox. After changing the Caption of the Label, one can read its width to fetch the size needed for the text.

The trick to make the resizing work lies in the Function SetWidths behind the userform (as shown on the previous page). I pass an array of character counts to the function (containing the maximum # of characters for each column to be shown in the listbox). Then for each column I change the caption of the label to an equal amount of characters as the value in the array. I use the same character, since the Font for the label is proportional. Thus, the letter used will determine what width the label gets.
Then I read the label's width and string that value together to form the ColumnWidths string (widths delimited by a ;).

The tricky part are the properties of the label. Set the WordWrap to False and the AutSize to True to make this work.

The end results looks like this:

Great, isn't it?