ComboBox.ListWidth question
 

Hello,
I have a form with several comboboxes. However, if the text from the
RowSource is longer than the combobox itself, I'd like the _list_ to
become wider (not the combobox), so that the longest entry in my
RowSource Range fits in.

However, the Property Inspector only lets me use " x pt" values under
ListWidth, but I don't want to provide a fixed width for my boxes.

Does anyone know how this can be done?

Thanks,Joepy

ComboBox.ListWidth question
 

Joepy

You could use a sub like this in the drop button click event to resize based
on the list

Private Sub ComboBox1_DropButtonClick()
Dim Rng As Range
Dim cell As Range
Dim MaxLen As Long

Set Rng = Me.Range(Me.ComboBox1.ListFillRange)

For Each cell In Rng.Cells
MaxLen = Application.Max(Len(cell.Text), MaxLen)
Next cell

Me.ComboBox1.ListWidth = Application.Max(MaxLen * 5, 30)
End Sub

This example is from a combobox on a sheet, so if you are using a userform,
you may need to modify it slightly.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Joepy" wrote in message
om...
Hello,
I have a form with several comboboxes. However, if the text from the
RowSource is longer than the combobox itself, I'd like the _list_ to
become wider (not the combobox), so that the longest entry in my
RowSource Range fits in.

However, the Property Inspector only lets me use " x pt" values under
ListWidth, but I don't want to provide a fixed width for my boxes.

Does anyone know how this can be done?

Thanks,Joepy