change size of in cell drop down list

[Guest]

Can anyone show me how to change the size of a drop down list in a cell
validation? I have a number of cells next to each other, but in clicking the
drop down list for the first cell for example, the list width is not going
over the adjacent cell and thereby not showing sufficient content. I cannot
increase the cell width, otherwise my problem was solved.

 

[Debra Dalgleish]

You can't control the data validation list width -- it's controlled by
the column width. You can use programming to make the column temporarily
wider. There's sample code here:

http://www.contextures.com/xlDataVal08.html#Wider

 

[Guest]

Debra

I did copy the code but nothing happens
Do I need to do anything else after copying and must I do the same for every
column applicable?

ps : by chance my column is also D

 

[Debra Dalgleish]

Is security set to medium, and are macros enabled in the workbook?
Did you paste the code onto the worksheet code module, not a regular
code module?

To use the code for multiple columns, you could use Select Case, e.g.:

'=========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4
Columns(4).ColumnWidth = 20
Columns(6).ColumnWidth = 5
Case 6
Columns(4).ColumnWidth = 5
Columns(6).ColumnWidth = 20
Case Else
Columns(4).ColumnWidth = 5
Columns(6).ColumnWidth = 5
End Select
If Target.Column = 4 Then
Else
End If
End Sub

'============================