Width of drop-down list

[Zilla]

First off, thanks for answering all my (numerous) questions!
Now this one...

I have Validation on a cell against a list. However the
width of the List entries are wider than the width of
the cell, hence the rhs of the list is cropped, rendering
it useles in this case. I don't want to adjust the width
of the cell.

Can I adjust the width of a drop-down list so it shows
all the chars. I have, regardless of how wide the cell
is I have the Validation on against the list? Once the
user chooses from the list, the original width of the
cell persists.

 

[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

 

[Zilla]

You're an expert! Thanks!

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

 

[Zilla]

I actually modified Debra's example this way, since I wanted to
this this on several colums, but one col. at a time, with the current
col being expanded, and the previous col going back to the orig
width. I also wanted to perform the operation only on cols after
col# 6.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevCol As Integer
Dim colWidth As Double
Dim bigWidth As Double
colWidth = 8.11
bigWidth = 20
If Target.Count = 1 Then
If Target.Column <> prevCol And Target.Column > 6 Then
Target.Columns.ColumnWidth = bigWidth
If prevCol > 0 Then
Columns(prevCol).ColumnWidth = colWidth
End If
Else
If Target.Column <> prevCol And prevCol > 0 Then
Columns(prevCol).ColumnWidth = colWidth
End If
End If
prevCol = Target.Column
Else
Target.Columns.ColumnWidth = colWidth
End If
End Sub