How do I make the drop-down portion of a list wider than the cell?


J

jtjlindsey

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?
 
Ad

Advertisements

Ad

Advertisements

G

Gord Dibben

The code from Debra that you posted below works fine for me in Excel 2003.

Did you copy the code into the sheet module as Debra instructs?

Does the column width increase when you select a cell in column C?

With a width of 40 I can fit in.............

Gord Dibben 1234 Main St. Anycity, province postal code


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top