Sizing Listbox Columns
In my DDoE Quick TTC utility, I split text out into separate listbox columns. That means the column widths are always changing. Jan Karel has a very nice write-up on autosizing listbox columns. Based on that, I wrote the below procedure. I used the hidden label technique that JKP suggested. Instead of passing in the number of characters, I just used the actual characters in the listbox. It makes the sub a lot less portable, but it’s suitable for my purpose.
Private Sub SetColumnWidths()
Dim i As Long, j As Long
Dim sColWidths As String
Dim dMax As Double
For i = 0 To Me.lbxText.ColumnCount – 1
For j = 0 To Me.lbxText.ListCount – 1
Me.lblHidden.Caption = Me.lbxText.Column(i, j) & “MM”
If dMax < Me.lblHidden.Width Then
dMax = Me.lblHidden.Width
End If
Next j
sColWidths = sColWidths & CLng(dMax + 1) & “;”
dMax = 0
Next i
Me.lbxText.ColumnWidths = sColWidths
End Sub
Dim i As Long, j As Long
Dim sColWidths As String
Dim dMax As Double
For i = 0 To Me.lbxText.ColumnCount – 1
For j = 0 To Me.lbxText.ListCount – 1
Me.lblHidden.Caption = Me.lbxText.Column(i, j) & “MM”
If dMax < Me.lblHidden.Width Then
dMax = Me.lblHidden.Width
End If
Next j
sColWidths = sColWidths & CLng(dMax + 1) & “;”
dMax = 0
Next i
Me.lbxText.ColumnWidths = sColWidths
End Sub
I add a couple of capital Ms to the end of the text before I measure the width. It gives a nice buffer. Then I just store the maximum that the width of the hidden label gets and create a column widths string from all of the maximums. Here it is in action:
Why not using Excel ?
sq = Sheets(1).UsedRange
ListBox1.List = sq
Sheets(1).UsedRange.Columns.AutoFit
ListBox1.Width = Sheets(1).UsedRange.Columns(UBound(sq, 2) + 1).Left * 1.2 + 3
Application.Wait Now + TimeValue(“00:00:01″)
ListBox1.ColumnCount = UBound(sq, 2)
For Each cl In Sheets(1).UsedRange.Columns
c0 = IIf(c0 = “”, “”, c0 & “;”) & cl.Width * 1.2
Next
ListBox1.ColumnWidths = c0
End Sub
Nice! Using ‘magic numbers’ like len() x Font.Size x 1.2 has never really worked; it had never occurred to me that I could use a label’s embedded ability to set it’s own width.
I like this trick, useful for my current project. Thanks for sharing.
You’re not in Seattle this year ? Or are you blogging during the quiet Ballmer sessions ?
Nope, I didn’t make it this year. Too many other commitments this Winter/Spring.