Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Simplify VBA (2003)

    Loungers,

    I'm using this code that I obtained from Contextures. Rather than duplicating each part and changing the column reference, can this be simplified to add additional columns into one section (I hope that makes sense)?

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 10 Then
    Target.Columns.ColumnWidth = 25
    Else
    Columns(10).ColumnWidth = 5
    End If
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 11 Then
    Target.Columns.ColumnWidth = 25
    Else
    Columns(11).ColumnWidth = 5
    End If
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 17 Then
    Target.Columns.ColumnWidth = 25
    Else
    Columns(17).ColumnWidth = 5
    End If
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 18 Then
    Target.Columns.ColumnWidth = 25
    Else
    Columns(18).ColumnWidth = 5
    End If
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 23 Then
    Target.Columns.ColumnWidth = 25
    Else
    Columns(23).ColumnWidth = 5
    End If
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 24 Then
    Target.Columns.ColumnWidth = 25
    Else
    Columns(24).ColumnWidth = 5
    End If
    End Sub

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simplify VBA (2003)

    That code is really meant to work on one column only. The effect is rather unnerving if you apply it to multiple columns.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static lngCol As Long
    If Target.Count > 1 Then Exit Sub
    Select Case Target.Column
    Case 10, 11, 17, 18, 23, 24
    Target.Columns.ColumnWidth = 25
    End Select
    Select Case lngCol
    Case 10, 11, 17, 18, 23, 24
    Columns(lngCol).ColumnWidth = 5
    End Select
    lngCol = Target.Column
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Simplify VBA (2003)

    Thanks Hans,

    That looks much better and much simpler. For my interest and to help me understand some of the principles of VBA- why is the effect of the original

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Simplify VBA (2003)

    "Unnerving" was probably not the correct term (English is not my native language), I meant that having several columns widening/narrowing as you move across the worksheet instead of just one makes me nervous.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Simplify VBA (2003)

    Ok Thanks Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •