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

Hi all,

For a project I needed a quick way to display the content of an array to the user. I didn’t want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like:

Description Before After
Cell Errors 100 10
Corrupt Names 1000 0
Unused styles 232 0

So I figured I’d put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown.
I wanted it to look like this:
That proved far from easy…

Read on…



Jan Karel Pieterse

Posted in Uncategorized

5 thoughts on “Showing An Array On A Form; Autosizing ColumnWidths Of A ListBox

  1. This is exactly how I do my column sizing for listboxes.

    But there is no need to repeat the resize if you set the Width property to a larger value. Try setting it to 9999 and comment out the duplication.

    [i]Note: I also added this comment to your own sites comment page[/i]

  2. A few comments about the technique:
    1. If you have the lblHidden set to not wraptext, you can keep it autosizing and there’s no need to set the width to anything yourself or duplicate the setting. Just pop the text in and read the width.
    2. I don’t understand why you’re using cell length counts and repeated ‘m’ characters. Why don’t you just put the actual text in the autosize box?
    3. To avoid international issues, it’s a good idea to take the integer value (rounding up) of the label width when building the columnwidth string.

  3. Andy: Good point.

    1. Got it. Finally solved that little mystery: WordWrap was the property I overlooked. I’ll update the article and the DL to reflect the changes.
    2. I thought it might be more efficient than writing the caption for the label each time, fetching the max width, …. So I just collect the max # of characters and set the width using a repeated wide character.
    3. Silly mistake…

  4. Jan Karel:
    2. Slightly more efficient, yes, but there’s a huge difference between iiiiii and wwwwww. I’ve always gone for the full-text approach and never had a *noticeable* delay.

  5. Hi,

    I was trying to solve this same problem recently (resize the columns of listbox). But, as the width of some of the text values varied too much (one extra long string made the control look weird), I gave up and used ListView control instead; users drag the width if they want, and col headings are embedded, which looks nice. Might use some of your ideas to set a better default width though.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.