Make Validation List Wider - Excel 2003

[cmarion]

I found the following post from 2006 and the code works great, but it is for
one column, how do I alter the code for columns C through AX so the temporary
widening happens in each column?

"You cannot word-wrap a list for DV.

What you can do is use event code to make the list wider when you click on the
drop-down.

See Debra Dalgeish's site for code.

http://www.contextures.on.ca/xlDataVal08.html#Wider"

 

[Debra Dalgleish]

You can change the target columns:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column >= 3 And Target.Column <= 50 Then
Target.Columns.ColumnWidth = 20
Else
Columns(4).ColumnWidth = 5
End If
End Sub

 

[cmarion]

This works for expanding the column width, but does not return the column to
the smaller size when no longer selected.

Thank you.

 

[Debra Dalgleish]

This will reset all the affected columns to the same width.
If you need them different widths you could specify in the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("C:AX").ColumnWidth = 5
If Target.Column >= 3 And Target.Column <= 50 Then
Target.Columns.ColumnWidth = 20
Else
Columns("C:AX").ColumnWidth = 5
End If
End Sub

 

[cmarion]

Wonderful, thank you for your help.

This will reset all the affected columns to the same width.
If you need them different widths you could specify in the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("C:AX").ColumnWidth = 5
If Target.Column >= 3 And Target.Column <= 50 Then
Target.Columns.ColumnWidth = 20
Else
Columns("C:AX").ColumnWidth = 5
End If
End Sub




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com