Fitting text in a Listbox

[AlexanderBB]

This is a nightmare... How are you supposed to fit text of a unknown length into a listbox control and see it all ?
There's no autowidth, or multiline etc. Or am I wrong?

I see a suggestion to use a listview control and will try that.
Or perhaps a 3rd party control you pay for? But does that cause issues giving the workbook to someone else?
Thanks.

 

[Kenneth Hobson]

Hopefully, you know your data well enough to know what size would fit using that font. Proportional fonts make that tough but one can make a good estimate if needed.

If a horizontal scrollbar would suffice, simply set columnwidths longer than the width. e.g.

Private Sub UserForm_Initialize()
  ListBox1.AddItem "Fourscore and seven years ago our " & _
    "fathers brought forth on this continent a new nation"
  ListBox1.ColumnWidths = ListBox1.Width * 10
End Sub

 

[AlexanderBB]

Googling around has brought up a lot of similar requests. The problem is the length of text isn't known in advance.
And the lack of Autosize (which exists for lablels but is bugged in Excel 2007).
My layout doesn't allow for a horiz scrollbar, I have to wordwrap.

 

[Jaafar Tribak]

Is the listbox on a worksheet or on a userform ?

 

[AlexanderBB]

On a Frame in a Userform.

 

[mikerickson]

Create a text box with the same formatting as the Listbox.
Put the longest text into the text box. Autosize the text box. Read its width.
Add 16 for the width of the scroll bar and set the width of the list box.

 

[Jaafar Tribak]

On a Frame in a Userform.

I was thinking maybe I could some API calls on the listbox because it provides a hwnd but then again I thought this would be more or less the same as dereasing the size of the listbox font via its Properties window or via vba.

Have you considered decreasing the size of the listbox font depending on the length of characters ?

 

[AlexanderBB]

Ahh, this is brilliant. Thank you Rick I had done almost what you said, but with a label. I found it worked perfectly in Excel 2003 but in 2007 the label would not autosize correctly.
The text to display is not known and can be any length. Its in a list control to enable a click event on each line. I'm using ms sans serif 8 so it's pretty small already.

This is what I cobbled together. Wordwrap and Autosize are True in textbox properties and Visible false.

Sub BuildList(Dat As String, ListSide As Integer)

    Dim ListHeight As Long, Done As Boolean, NextSpacePosition As Integer, PreviousPlace As Integer, Labelwidth As Long
    With frmSerial

        Do
            .txtWidth = Dat
            If .txtWidth.Width > 250 Then
                    Do
                        PreviousPlace = NextSpacePosition
                        NextSpacePosition = InStr(PreviousPlace + 1, Dat, " ")
                        .txtWidth = Left(Dat, NextSpacePosition)
                    Loop Until .txtWidth.Width > 250
                    .Controls("lstAlbum" & ListSide).AddItem Left(Dat, PreviousPlace)
                    Dat = Mid(Dat, PreviousPlace + 1)
                    NextSpacePosition = 0
                Else
                    Done = True
            End If
        Loop Until Done
        
        .Controls("lstAlbum" & ListSide).AddItem Dat
        ListHeight = .Controls("lstAlbum" & ListSide).ListCount * 10
        .Controls("lstAlbum" & ListSide).Height = ListHeight
    End With
End Sub

The list control is on a Frame with some other labels and othr contols so the whole thing scrolls together.
I found the text on some of these would show in the wrong size (even though they were the right values). I think this cam right when I chnge the variable
with each Top value from integer to Long. The only thing now is to figure out how to add an indent to rows 2 and above.

 

[AlexanderBB]

After a bit more trying thing out, all that code could be redundant IF I can read the LFs in the textbox (after MultiLine is set True).

Can an API routine do that ?

 

[Kenneth Hobson]

Private Sub CommandButton1_Click()
  With TextBox1
    .SetFocus
    MsgBox TextBox1.LineCount
  End With
End Sub