Geek Answers Handbook

Dynamically adjust dropdown width in Excel VBA

I am trying to adjust combobox (in Excel VBA) so that its width is automatically adjusted according to the length of the longest string it contains.

I am trying to create a drop-down list (using a combobox in a form called "WorksheetSelectionForm"), which after opening a specific workbook appears on the screen and allows the user to choose which worksheets they want to open.

I want the combobox width to adjust to the length of the longest text string in the drop-down list. My drop-down list currently contains three items (the names of the worksheets that currently exist in the book). They are as follows:

  • Profit and Loss Statement (23 characters)
  • Balance (13 characters)
  • Cash Flow Statement (15 characters)

You can add more worksheets to the workbook, and therefore more items are added to the drop-down list, so I don’t want to just fix the combobox width at 23 points (the length of the longest line in the drop-down list).

I refer to the following thread from OzGrid for ideas (see entry # 3): http://www.ozgrid.com/forum/showthread.php?t=55098 . Their proposed solution is given below:

Dim iWidth As Double 
ComboBox1.AutoSize = True 
iWidth = 0 

For i = 0 To ComboBox1.ListCount - 1 
    ComboBox1.ListIndex = i 
    If iWidth < ComboBox1.Width Then 
        iWidth = ComboBox1.Width 
    End If 
Next 

ComboBox1.Width =  iWidth 
ComboBOx1.AutoSize = False 
ComboBox1.ListCount = 0

The problem with this solution is that the code ComboBox1.Widthin the if-then statement does not actually display the length of the combobox element that is currently in focus in the next loop.

Below is the code that I have written so far:

Private Sub Workbook_Open()

Dim Sheet As Worksheet, CmBox As MSForms.ComboBox, LWidth As Double, i As Integer
Set CmBox = WorksheetSelectionForm.ComboBox_Worksheets
LWidth = 0

'Populate the drop-down list with the names of the worksheets
For Each Sheet In Worksheets
    CmBox.AddItem Sheet.Name
Next Sheet

'Find out the length of the longest string in the combobox
For i = 0 To CmBox.ListCount - 1
    CmBox.ListIndex = i
    If Len(CmBox.Value) > LWidth Then
        LWidth = Len(CmBox.Value)
    End If
Next i

'Set the combobox width to the length of the longest string in the combobox
CmBox.ListWidth = LWidth

'Show the form on screen
WorksheetSelectionForm.Show

End Sub

, -, . , ( ). ?

, ( , ):

Private Sub ComboBox_Worksheets_Change()

'Activate the worksheet whose name has been selected in the combobox
Sheets(ComboBox_Worksheets.Value).Activate

'Close the form
Unload WorksheetSelectionForm

End Sub
+4
2

. API. , , . vb

, API:

  • GetCharABCWidths ( )
  • GetChartABCWidthsFloat
  • GetCharWidth (, )
  • GetCharWidth32 (, )
  • GetCharWidthFloat
+1

, :

Private Sub Workbook_Open()

Dim Sheet As Worksheet, CmBox As MSForms.ComboBox, LWidth As Double, i As Integer
dim Wb as workbook
load WorksheetSelectionForm
with WorksheetSelectionForm
    Set CmBox = .ComboBox_Worksheets
    'LWidth = 0

    'Populate the drop-down list with the names of the worksheets
    with cmBox
        .clear
        for each Wb in workbooks
            For Each Sheet In WB.Worksheets 'i wasn't sure your way works for filling the list, did you verify it ?, so i do it my way
                h = Sheet.Name
                .AddItem h
                if len(h)>Lwidth then LWidth = Len(h) 'no need to loop again when list is full
            Next Sheet
        next Wb
    end with

    'Find out the length of the longest string in the combobox
    'For i = 0 To CmBox.ListCount - 1
    '    tmp_Length = len(CmBox.List(i))    'this is an other way of doing it, without changing the cmBox value (could trigger events)
    '    If tmp_Length > LWidth Then
    '        LWidth = tmp_Length
    '    End If
    'Next i

    'Set the combobox List width to the length of the longest string in the combobox
    CmBox.ListWidth = LWidth*8 'according to the list Text Font size , you will need to adjust the *8

    'Show the form on screen
    .Show
end with

Sub

0

More articles:


All Articles