J
Joe M.
I have some code that widens certain colums when clicked upon so I can see
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long
myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)
If Target.Count > 1 Then Exit Sub
For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr
End Sub
the pick list entire width but don't need the column to stay that width after
selecting the pick list. The code works correctly, however I don't need it to
apply to the entire column, only to certain cells or a range. The code is
below. What changes would I need for particular cells or a cell range within
a column?
Thanks,
Joe M.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColumns As Variant
Dim myWidthSelected As Variant
Dim myWidthNormal As Variant
Dim iCtr As Long
myColumns = Array("a", "b", "c", "f")
myWidthSelected = Array(30, 40, 30, 40)
myWidthNormal = Array(21, 26, 21, 4.14)
If Target.Count > 1 Then Exit Sub
For iCtr = LBound(myColumns) To UBound(myColumns)
If Intersect(Target, _
Me.Cells(1, myColumns(iCtr)).EntireColumn) Is Nothing Then
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthNormal(iCtr)
Else
Me.Cells(1, myColumns(iCtr)).EntireColumn.ColumnWidth _
= myWidthSelected(iCtr)
End If
Next iCtr
End Sub