Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox Question | Excel Discussion (Misc queries) | |||
another combobox question | Excel Discussion (Misc queries) | |||
ComboBox Question | Excel Worksheet Functions | |||
combobox Question | Excel Discussion (Misc queries) | |||
VBA Form ComboBox question | Excel Worksheet Functions |