Autosize dropdown list from combobox to fit text in it.

[Bandito1]

Hello all,

I got two comboboxes in my worksheet names cmbDepartment and cmbFilter.
When a department is selected you can filter the department with cmbFilter.

To fill cmbDepartment i filled in the ListFillRange. To populate cmbFilter i use the following code:

Private Sub cmbDepartment_Change()Me.cmbFilter.ListFillRange = Me.cmbDepartment
End Sub

I hope this is clear.
Now my question:

When i press cmbFilter the values in the dropdown exceed the width of the dropdown area.

I would like that the dropdown area auto fits the longest text in it without changing the actual combobox filter.

Someone knows how to accomplish this?

 

[Logit]

.
The ActiveX ComboBox Dropdown has a property for AutoSize the width.

Set it to true.

 

[Bandito1]

Then the Combobox itself changes of width and not the dropdown list..

I would like that only the dropdown list changes width and not the combobox itself

 

[My Aswer Is This]

I do not think that's possible​

Then the Combobox itself changes of width and not the dropdown list..

I would like that only the dropdown list changes width and not the combobox itself

 

[Yongle]

Try this....

- set the width to a size sufficient allow user to see everything
- cell ref in LostFocus event can be any cell with correct width
- cell ref in Change event can be any cell in the worksheet

Private Sub ComboBox1_DropButt*******()  [B]see below[/B]
    ComboBox1.Width = ComboBox1.[COLOR=#ff0000]Width * 4[/COLOR]
End Sub

Private Sub ComboBox1_LostFocus()
    ComboBox1.Width = [COLOR=#008080]Range("G7").[/COLOR]Width
End Sub

Private Sub ComboBox1_Change()
   [COLOR=#a52a2a] Range("G7")[/COLOR].Activate
End Sub

EDIT
For some reason the forum upload does not like ComboBox1_ Drop Button Click () without spaces!!

 

[Bandito1]

Almost perfect!

One detail; if i press the drop button twice the combobox doubles in size, i think because the code fires 2 times?
Is it possible to stop this? So the code fires only once when the drop button is clicked?

 

[Akuini]

Another way:
Change the width to suit:

Private Sub ComboBox2_DropButt*******()
ComboBox2.Width = 300

End Sub

Private Sub ComboBox2_GotFocus()
ComboBox2.Width = 300

End Sub

Private Sub ComboBox2_LostFocus()
ComboBox2.Width = 100
End Sub

 

[Yongle]

thanks for the feedback

Is it possible ... the code fires only once when the drop button is clicked?

Simplest way is to add a condition that is always true after first doubling
... something similar to this ...

Private Sub ComboBox1_DropButt*******()
    If ComboBox1.Width > Range("G7").Width Then Exit Sub
    ComboBox1.Width = ComboBox1.Width * 2
End Sub

 

[Bandito1]

thanks for the feedback



Simplest way is to add a condition that is always true after first doubling
... something similar to this ...

Private Sub ComboBox1_DropButt*******()
    If ComboBox1.Width > Range("G7").Width Then Exit Sub
    ComboBox1.Width = ComboBox1.Width * 2
End Sub

This did the trick, thank you once again.
Now my last question

A small thing but if it can be fixed it is 100% perfect.
I use Y1 as Width range. Every time i press a value in my combobox cell Y1 get's selected.
Is it possible to hide that? That Y1 doesn't show a green line around it because it is selected?