How about:

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", "c", "e")
myWidthSelected = Array(3, 18.42, 33)
myWidthNormal = Array(1, 10, 20)

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

Personally, I wouldn't do this kind of thing--I'd just widen the column to begin
with.

I hate that I'd lose the edit|Undo and Edit|Redo stack.



Steve E wrote:

Dave,

Thanks for taking this on.

If I understand correctly, this changes any column width to 18, right? It
looks like this changes the width to 18 on entry and then resets to 15.38 --
what if I want to reset to different widths based on the column?

My commented out section of code is an example of one of the other columns
where the 'normal' width is 21 but I need it to widen to 28 so that you can
read all of the input selections in the drop down list. These widths vary
from column to column based on the maximum text width and my formatting
limitations (on overall width of the sheet).

Clear as mud?

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRng As Range

Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn

If Target.Count 1 Then Exit Sub

'reset all those column widths
myRng.ColumnWidth = 15.38
If Intersect(Target, myRng) Is Nothing Then
'do nothing
Else
Target.EntireColumn.ColumnWidth = 18
End If
End Sub

Include a cell from any column you need in this line:
Set myRng = Me.Range("a1,g1,L1:q1").EntireColumn

Steve E wrote:

My VB is very weak (trying to learn). I've followed the instructions on
Debra Dalgleish's site for adding code to the worksheet and it works fine for
a single column but I don't know how to change the code when I have multiple
columns that need to be temporarily widened so that you can see the entire
selection in the drop down list.

My worksheet code looks like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 13 Then
Target.Columns.ColumnWidth = 18
Else
Columns(13).ColumnWidth = 15.38
End If
End Sub

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' If Target.Count 1 Then Exit Sub
' If Target.Column = 3 Then
' Target.Columns.ColumnWidth = 28
' Else
' Columns(3).ColumnWidth = 21
' End If
'End Sub

I have 10+ columns of drop downs that are too wide to view in the
spreadsheet (I have the columns formatted to wordwrap so that the sheet isn't
too wide to be usable when completed).

How do I need to modify the code for the extra columns?

Thanks,

--

Dave Peterson


--

Dave Peterson