Autosizing Combobox/Listbox Columns (addition to Lebans code)

[Guest]

Hello all,

This isn't a question, just an addition to some code I found at Lebans site.

I struggled (and failed) for a long time trying to find a way to resize
columns of listboxes and comboboxes to automatically fit the text of the
control.

I found Lebans solution for the problem (found here:
http://www.lebans.com/listboxcolumnresize.htm) and it worked great.

The one problem I had was getting the dropdown of combo boxes to auto fit
the total text (of all columns). Using 'auto' as the list width didn't work.

Anyway, I've modified Lebans code to take care of it for me. I only changed
one function, which is posted below. All other code I left as originally
written by Lebans. It's a minor change, but I thought I'd share it. Hope it
helps you.


Public Sub AutoSize()

' Junk vars
Dim lngRet As Long
Dim ctr As Long
Dim strTemp As String
Dim lngWidth As Long
Dim lngTotalWidth As Long

' Temp array to hold calculated Column Width
Dim lngArray() As Long
' Temp array to hold calculated Column Widths
Dim strArray() As String

ReDim lngArray(UBound(sngWidthArray))
ReDim strArray(UBound(sngWidthArray))

For ctr = 0 To m_Control.ColumnCount - 1
lngArray(ctr) = GetColumnMaxWidth(m_Control, ctr) + m_ColumnMargin
Next ctr

lngTotalWidth = 0

' Build the ColumnWidths property
For ctr = 0 To UBound(lngArray)
' Init var
lngWidth = lngArray(ctr)

If ctr <> UBound(strArray) Then
strArray(ctr) = lngWidth & ";"
Else
strArray(ctr) = lngWidth
End If

lngTotalWidth = lngTotalWidth + lngWidth

Next ctr

' Build ColumnWidths property
strTemp = ""
For ctr = 0 To UBound(strArray)
strTemp = strTemp & strArray(ctr)
Next

' Update the property
m_Control.ColumnWidths = strTemp

' if the control is a combo box
If m_Control.ControlType = acComboBox Then
' set the width of the dropdown to the width of the text
' plus 300 to account for the scroll bar
m_Control.ListWidth = lngTotalWidth + 300
End If

End Sub


Cheers,
Jay