Excel drop down list wider than cell
Ngày đăng:
12/12/2021
Trả lời:
0
Lượt xem:
135
I have a timesheet spreadsheet. I have created a list in a separate worksheet
within the same workbook that contains a name, address, city, & state in each cell in column A. The text is wrapped and the cell is double height to display all of the data. In my timesheet tab, the cells are the same width and height and set to wrap text as well. The problem is that when I click on the drop down list arrow, the drop down portion is the same width as the cell and the text is displayed in a single line. I can not read enough of the information to make the correct selection. I found and tried this: http://www.contextures.com/xlDataVal08.html#Wider The Data Validation dropdown is the width of the cell that it's in, to a minimum of about 3/4". You could use a SelectionChange event to temporarily widen the column when it's active, then make it narrower when you select a cell in another column. For example, with Data Validation cells in column D: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 4 Then Target.Columns.ColumnWidth = 20 Else Columns(4).ColumnWidth = 5 End If End Sub To add this code to the worksheet: 1. Right-click on the sheet tab, and choose View Code. 2. Copy the code, and paste it onto the code module. 3. Change the column reference from 4 to match your worksheet. I followed these instructions and changed the column reference to 3 because my cell in the timesheet with dropdown list is in column C. The actual list of data is in column A on a separate worksheet within the same workbook. I also changed the "Target.Columns.ColumnWidth = 160" just so I would be sure not to mistake seeing the change. When I click on the dropdown arrow, the dropdown list is still the same width as the cell. I originally created the dropdown using data validation. Is that what is keeping this from working? If so, how can I correct it? |