Data validation: wider window on 14 columns

[Guest]

I need 14 columns to temporarily expand so a data validation window can be
read.

Can the following solution from
http://www.contextures.on.ca/xlDataVal08.html#Wider be used on multiple
columns?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 37 Then
Target.Columns.ColumnWidth = 10
Else
Columns(37).ColumnWidth = 5
End If
End Sub

 

[Dave Peterson]

Maybe something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range

If Target.Count > 1 Then Exit Sub

'adjust the range to what you want
Set myRng = Me.Range("a:b,x:y,d1:d5")

'fix all the columns
myRng.EntireColumn.ColumnWidth = 5

If Intersect(Target, myRng) Is Nothing Then
'do nothing
Else
'adjust that single column
Target.EntireColumn.ColumnWidth = 10
End If
End Sub

 

[Guest]

It worked great! Is there any way to eliminate the "blinking" in the
worksheet as the cursor moves to another cell?
--
Jim C

Maybe something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range

If Target.Count > 1 Then Exit Sub

'adjust the range to what you want
Set myRng = Me.Range("a:b,x:y,d1:d5")

'fix all the columns
myRng.EntireColumn.ColumnWidth = 5

If Intersect(Target, myRng) Is Nothing Then
'do nothing
Else
'adjust that single column
Target.EntireColumn.ColumnWidth = 10
End If
End Sub

 

[Dave Peterson]

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRng As Range

If Target.Count > 1 Then Exit Sub

'adjust the range to what you want
Set myRng = Me.Range("a:b,x:y,d1:d5")

application.screenupdating = false '<-- added

'fix all the columns
myRng.EntireColumn.ColumnWidth = 5

If Intersect(Target, myRng) Is Nothing Then
'do nothing
Else
'adjust that single column
Target.EntireColumn.ColumnWidth = 10
End If

application.screenupdating = True '<-- added

End Sub

It worked great! Is there any way to eliminate the "blinking" in the
worksheet as the cursor moves to another cell?