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
--
Regards,
Tom Ogilvy
"Rene" wrote:
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