Width of drop down in data validation

[Rene]

Hi experts!

I found this great code to temporarily change the width of a column to
overcome problems with the width of the drop down field (for data
validation):

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

Checkout: http://www.contextures.com/xlDataVal08.html

PROBLEM: I want to make this code applicable to an entire range of
columns (columns 17-85) and not only to a single one (in this case
column 4). Is there any way to tweak the code? I can change the "If
Target.Column =4 Then" to "If TargetColumn >17 And <85" but then the
code after "Else" will not work.

Thanks for your help.
Rene

 

[Tom Ogilvy]

Use a public variable at the top of the module to "remember" what column you
last increased the width of.

public col as Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if not col is nothing then
col.EntireColumn.ColumnWidth = 5
set col = nothing
end if
If Target.Count > 1 Then Exit Sub
If Target.Column >= 17 and Target.Column <= 85 Then
Target.Columns.ColumnWidth = 20
set col = Target.EntireColumn
end if
End Sub

 

[Rene]

Hi Tom!

Thanks for the quick response! Just what I was looking for!

Thank you!
Rene