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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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?