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

[GTO]

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

Bruce:

Please read Here (http://excelguru.ca/node/7)

Mark

[mctabish]

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

Sorry...

[tpoynton]

ah....

I was curious, so I mocked something up. not elegant, but it seems to be close

[mctabish]

That DOES do what I need it to do! Now I just need to figure it out for a list created by an ARRAY!

Thanks!
Bruce:cloud9: :bow:

[mctabish]

Here is what I have working for now. I see that your solution relies on the .Columns.AutoFit, since an array does not have an autofit, I have saved the longest array element to a very hidden ws, and then autosize that. It is working the way I need it to.


Private Sub UserForm_Initialize()
Dim cl As Range
Dim iMax As Long
Dim iPopCol As Long
Dim t_Array(17) As String
Dim bUseRowSource As Boolean
Dim rPopRange As Range
iPopCol = 3
t_Array(0) = "sadflkja;sldjf"
t_Array(1) = "asdfasd"
t_Array(2) = "asdf"
t_Array(3) = "asdfasdfasdfasdfas"
t_Array(4) = "sadflkja;sldjf"
t_Array(5) = "asdfasd"
t_Array(6) = "asdf"
t_Array(7) = "asdfasdfasdfasdfas"
t_Array(8) = "asdfasdfasdfasdfas"
t_Array(9) = "sadflkja;sldjf"
t_Array(10) = "asdfasd"
t_Array(11) = "asdf"
t_Array(12) = "asdfasdfasdfasdfas"
t_Array(13) = "sadflkja;sldjf"
t_Array(14) = "asdfasd"
t_Array(15) = "asdf"
t_Array(16) = "asdfasdfasdfasdfas"
maxlen = 0
For Z = 0 To 16
If Len(t_Array(Z)) > maxlen Then
maxlen = Len(t_Array(Z))
temp = t_Array(Z)
End If
Next
ThisWorkbook.Sheets("Sheet3").Cells(1, 1) = temp

With ThisWorkbook.Sheets("Sheet3")
Set rPopRange = .Range("A1")
rPopRange.Columns.AutoFit
For Each cl In rPopRange
With ListBox1
.AddItem (cl.Value)
If Len(cl) > iMax Then
iMax = Len(cl)
Label1.Caption = cl.Value
End If
.Font.Name = rPopRange.Font.Name
.Font.Size = rPopRange.Font.Size
End With
Next cl
MsgBox iMax
End With
ListBox1.Width = rPopRange.Width + 5
End Sub


Thanks much!

[GTO]

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

Sorry...

Hey Bruce,

I responded at the other thread as well, but forgot you say thank you for acknowledging so quickly.

Thank you very much.

If you run into any problem with the .List is from an array part, I'd bet we could help:)

Mark