Auto width of a listbox on a userform?

[mctabish]

I am trying to create a userform that is reusable by turning on and off diff
objects, and reusing objects
I am running into a little difficulty of resizing the list box for various lengths if items
Example would be if I have a list of items and the longest one is 93 chars long, I need a width of 672
When I have a list that the items are each 5 chars long, I need a listbox width of 92

For simplicity's sake, I am using Courier (supposed to be a monospaced font) pitch is 10. I would have figured that my width
would be simply a matter of finding the longest length in the list and then multiply that times some magical number that
represents the width of the letter (since monospaced, each letter should be the same).

But, with the 93 char long string, the "average length" of each letter seems to be 7.2
(672/93 = ~7.2) but for the shorter words, it seems to be wider at 18 (92/5 = ~18)

Is there a way to have the listbox size itself to the width of the strings?

Thanks
Bruce

 

[delaneyjm]

Maybe

yourFormName.ListBox1.AutoSize = True

 

[mctabish]

I tried and got the ole "Compile error: Method or data member not found"
Thanks for the suggestion though! It made sense. But regretibly, it failed.

 

[Norie]

Bruce

For simplicity's sake, what are you trying to do?

Seems like you are overcomplicated things.

For a start a listbox doesn't have an AutoSize property/method.

I've honestly never seen that used with the other types of control that do support it.

 

[mctabish]

I have several lists that I need to have the enduser select from. The list is various lengths as far as itemcount and length of each item. I am not concerned about how high.

I am trying to use the same form instead have having 4 different ones that do the same thing. I thought I could reuse the forms. Functionally, I have done this, but cosmetically, it leaves something to be desired. For example in one "mode" the list box elements are each 93 char. long and it look nice having the WIDE listbox, however, there are 2 modes that the listbox handles much shorter length items (about 5 chars wide in one case and 12 chars wide in the other. It just looks silly to have a REALLY WIDE listbox when a narrow one would work.

I did not think listbox had autosize but when delaneyjm suggested, I thought well it makes sense, and you know how well MS documents all of the properties.. I thought that it could be there

I know HOW to resize, but I just trying to figure out if there is a way to determine HOW wide.
Again, functionally it works, just looks wierd...

So this is what I am trying to do.

Thanks


Bruce

 

[GTO]

Inititated here within one minute of being initiated at: http://vbaexpress.com/forum/showthread.php?t=30416

Bruce:

Please read Here

Thank you.

Mark

 

[mctabish]

Sorry, just moved systems and meant to post here, but don't have all of my FAVORITES set up yet, and went to the wrong site (grey matter messup!)

Sorry...

 

[GTO]

...don't have all of my FAVORITES set up yet, and went to the wrong site (grey matter messup!)...

LOL. As one who suffers too many 'blonde moments', I certainly understand

I'm glad you were able to get a nice solution as well

Mark

 

[mctabish]

Here is the solution:
http://vbaexpress.com/forum/showthread.php?p=204646#post204646

 

[mikerickson]

This technique can also be adapted to autosize the height.
The additional 18 is to account for the possibility of a scroll bar.

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    
    Rem textbox properties that could be set a design time
    With TextBox1
        .Width = 10
        .MultiLine = True
        .WordWrap = False
        .Text = vbNullString
        .AutoSize = True
        .Visible = False
    End With

    For Each oneCell In Range("A1:A10").Cells
        ListBox1.AddItem CStr(oneCell)
        With TextBox1
            .Text = .Text & vbCr & CStr(oneCell)
        End With
    Next oneCell
   
    ListBox1.Width = TextBox1.Width + 18
End Sub