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?
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 SubClick Here Before Posting Data or VBA Code —> How To Post Data or Code.