Listbox Column Widths and Lengths -Autofit?

**jomili** · 12-09-2009, 05:04 PM

Please forgive me if this post shows up twice; my first attempt disappeared on me (the system logged me out when I tried to submit it).

I've got a Listbox in a User form that looks at data on another spreadsheet purely for informational purposes, not to be selected. I have two problems with what it's doing:

1) There are 23 columns, of varying widths. However the listbox uses a standard width, so some items are truncated and some have too much room.

2) The data changes, and the number of rows may change from 2 to 15000. I've set the RowSource to cover the ranged from A1:W15000, but if only 2-300 lines are selected the user can't grab the scrollbar button to scroll through, but must instead use the arrow, which is rather slow.

Is there any way to set the column widths to match the data, and the Range to only go as far as the last populated row?

Thanks in advance for your help on this.

**royUK** · 12-09-2009, 05:53 PM

here's one way, possibly not 100% accurate.

Please Login or Register  to view this content.

**jomili** · 12-10-2009, 09:44 AM

Roy,

I tried your solution, and got a Run-Time Error 9: Subscript out of range.

One possible error I see is putting this routine in the Initialize step. Once the user selects the region, the data is copied from from the SourceData/Sheet1 workbook/sheet to the Final/Data workbook/sheet, so if this runs in the Initialize subform routine that's prior to populating the data.

However, I tried it with data present, and that's when I got the Subscript out of range.

Here's the code as I tried to implement it. I'd appreciate your eyeballing it for errors:

Please Login or Register  to view this content.

**royUK** · 12-10-2009, 09:56 AM

Move it to the Activate event of the Form, but it might be better to post an eample workbook so that I can see the full picture

**jomili** · 12-10-2009, 11:05 AM

I haven't tried moving to activate yet, just commented it out for now so I could get you a working copy of what I've got so far (see the attachments).

**royUK** · 12-11-2009, 04:34 AM

There's a terrific amount of tidying up needed on your code. You have Option Explicit at radom places, this shol ppar once at the top of a Code module. You haven't declared all your variables.

I suggest yo Debug your code and orrect all the errors that show up first

**jomili** · 12-11-2009, 01:39 PM

Roy,

I've tidied up what I could. I don't know what variables haven't been declared. I also don't know how to use "Option Explicit"; I've seen it in a few routines, but don't know what it does, where it goes, how to use, or why to use it. If I try to put it at the top of a routine it jumps up to the bottom of the previous one.

I've attached updated spreadsheets for your review.

Going to the "Activate Event of the Form", the listbox form gets populated when the data is copied from SourceData to Final. No click happens, on the form, so the code won't be applied. How do we get it to apply automatically, or should I be using something different than a listbox to show the data?