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