How do I us If Then to do Worksheet_SelectionChange over multiple columns

[BarefootPaul]

Here is the code I wrote that doesn't work because I don't know how to set the column nnumber as an appropriate variable (I think that is the problem):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColumn As Integor
If Target.Count > 1 Then Exit Sub
iColumn = Target.Column
 
    If iColumn = 6 Or 10 Or 14 Or 18 Or 22 Or 26 Then
        Target.Columns.ColumnWidth = 20
    Else
        Columns(iColumn) = 3
    End If
 
    If iColumn = 7 Or 11 Or 15 Or 19 Or 23 Or 27 Then
        Target.Columns.ColumnWidth = 20
    Else
        Columns(iColumn) = 12
    End If
 
    If iColumn = 8 Or 12 Or 16 Or 20 Or 24 Or 28 Then
        Target.Columns.ColumnWidth = 20
    Else
        Columns(iColumn) = 3
    End If
 
End Sub

Can anyone help me with this?

Thanks

 

[wigi]

I am sorry, but it is very hard to follow the code.

What is a line like "Columns(iColumn) = 3" supposed to do?

Is my fear correct that you are filling every cell in that column with the number 3? Why?

Can you please take out typos like Integor ?

 

[BarefootPaul]

I swear I'm not usually that sloppy!

I fixed the typos and explained the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColumn As Integer
If Target.Count > 1 Then Exit Sub
iColumn = Target.Column
 
    If iColumn = 6 Or 10 Or 14 Or 18 Or 22 Or 26 Then 'Location Code Columns
        Target.Columns.ColumnWidth = 20 'Widen column to be able to see more in DropDown List
    Else
        Columns(iColumn).ColumnWidth = 3 'Narrow column to show only minimal content
    End If
 
    If iColumn = 7 Or 11 Or 15 Or 19 Or 23 Or 27 Then 'ME Proc Code Columns
        Target.Columns.ColumnWidth = 20
    Else
        Columns(iColumn).ColumnWidth = 12
    End If
 
    If iColumn = 8 Or 12 Or 16 Or 20 Or 24 Or 28 Then 'Proc Code Columns
        Target.Columns.ColumnWidth = 20
    Else
        Columns(iColumn).ColumnWidth = 3
    End If
 
End Sub

There are a repeated set of Dropdown lists that have long descriptions, but then after the choice is made it returns a code to the cell instead and only minimal space is needed for that to be stored/displayed.

Hope that makes more sense.

 

[wigi]

Why should this code be run from a Worksheet_SelectionChange event, instead of a normal command button?

 

[BarefootPaul]

Its just how I was made aware of the possibility to change the width of a column to make more room for a dropdown list to be displayed. Columns 6, 10, 14, 18, 22, and 26 all have descriptions for locations in them, but after choosing the location, a 2 digit number is returned and the column only needs to be wide enough to show that. There are more selections that would then need to be made on the worksheet.

The following code works perfect, but I didn't want to have to write it out for every column, when there is a clear pattern.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
 
   If Target.Column = 6 Then 'Column for Location Code
       Target.Columns.ColumnWidth = 20
   Else
       Columns(6).ColumnWidth = 3
   End If
 
   If Target.Column = 7 Then 'Column for ME Proc Code
       Target.Columns.ColumnWidth = 20
   Else
       Columns(7).ColumnWidth = 12
   End If
 
   If Target.Column = 8 Then 'Column for Proc Code
       Target.Columns.ColumnWidth = 35
   Else
       Columns(8).ColumnWidth = 6
   End If
End Sub

 

[wigi]

Did you know that you can copy/paste special the widths of columns?

For instance, in a (hidden) sheet, set the columns widths in the x first columns.

In the code, you can then copy/paste special the columns widths automatically.

Or, alternatively, a loop through the cells to set the column width one column at a time.

 

[wigi]

Some code to experiment with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count = 1 Then

        Select Case Target.Column
        Case 9, 13, 17, 21, 25: Columns(Target.Column).ColumnWidth = 3
        Case Else: Columns(Target.Column).ColumnWidth = 20
        End Select

    End If

End Sub

or:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count = 1 Then Columns(Target.Column).ColumnWidth = Sheets("HIDDEN SHEET").Columns(Target.Column).ColumnWidth

End Sub

In code example 2, I use a hidden sheet with the correct column widths.

 

[BarefootPaul]

Thanks, though I can't say I follow it all. I will try some things out and see what I come up with. You have given me some things to consider.

 

[wigi]

That's why I wrote that you should experiment

If there are questoins or follow-ups, please go ahead and post back in this topic.