• 0

Solved How To Change The Width of a Dropdown List in Excel

  • 0

I have a spreadsheet in Excel 2007 which for presentation reasons has narrow columns.
I want users to be able to make selections using dropdown lists but the names in the dropdown lists are longer than can be displayed in the narrow columns.
This is not a problem as I am using Text Orientation to rotate the selected items so they display okay.
Currently only the first few characters of the dropdown lists are visible to the user. I want to make the dropdown lists wider so that the contents can be read by the user without changing the narrow columns.

Any suggestions?


1 Answer

  1. I found this at:…

    You will need to modify it to use cell references that match your worksheet.

    [A1:A5] refers to the cells with the Drop Downs
    [E:E] refers to the column that contains the Source for the Drop Downs. The code assumes that the Source column is as wide as the data in those cells.

    Right-click the sheet tab for the sheet you want this to happen in and chose View code.
    Paste the following code into the pane that opens, then modify the cell references.

    Then go back into the sheet, select a drop down and see what happens.

    Note: The workbook will need to be saved as an xlsm or xlsb file and macros will need to be enabled on the user’s machine in order for this work.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myShp As Shape, Drp As Single
    On Error Resume Next
    'cells holding drop downs
    If Intersect(Target, [A1:A5]) Is Nothing Then Exit Sub
    If Target.Validation.Type = xlValidateList Then
        Set myShp = ActiveSheet.Shapes("Drop Down 1")
        Drp = myShp.Width - Target.Width
    'Column holding list, sized appropriately
        myShp.Width = [E:E].Width
        myShp.Left = Target.Left - myShp.Width / 2 + Drp * 2
    End If
    Set myShp = Nothing
    End Sub

    Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

    • 0