Solved How to change the width of a dropdown list in Excel

Microsoft Excel 2007
October 5, 2016 at 07:06:55
Specs: Windows XP
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?


See More: How to change the width of a dropdown list in Excel

Reply ↓  Report •


#1
October 5, 2016 at 07:47:53
✔ Best Answer
I found this at:

http://www.mrexcel.com/forum/excel-...

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.


Reply ↓  Report •

#2
October 5, 2016 at 09:02:40
Thank you DerbyDad03, I have not made it work perfectly but I am well on the way.

Reply ↓  Report •

Related Solutions


Ask Question