Results 1 to 9 of 9

Thread: VBA Code Help

  1. #1
    New Lounger
    Join Date
    Aug 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I'm trying to setup code so when someone clicks in Row 8 from Columns 4 - 18 the column width widens to 20 from 6.29. Then when the target leaves that column it goes back to 6.29. I found some example code on the web and modified it so it works, sort of. The problem is that when I click on any cell in the sheet all 15 columns resize to 6.29 which isn't very speedy. Does anyone know how I can change it so it only changes the width back when leaving the area of Row 8 from Columns 4 - 18 instead of any cell in the sheet? I have no real VBA skill I just find code and tweak so be patient with me.

    Thanks!

    Jamie

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column >= 4 And Target.Column <= 18 And Target.Row = 8 Then
    Target.Columns.ColumnWidth = 20
    Else
    Columns(4).ColumnWidth = 6.29
    Columns(5).ColumnWidth = 6.29
    Columns(6).ColumnWidth = 6.29
    Columns(7).ColumnWidth = 6.29
    Columns(8).ColumnWidth = 6.29
    Columns(9).ColumnWidth = 6.29
    Columns(10).ColumnWidth = 6.29
    Columns(11).ColumnWidth = 6.29
    Columns(12).ColumnWidth = 6.29
    Columns(13).ColumnWidth = 6.29
    Columns(14).ColumnWidth = 6.29
    Columns(15).ColumnWidth = 6.29
    Columns(16).ColumnWidth = 6.29
    Columns(17).ColumnWidth = 6.29
    Columns(18).ColumnWidth = 6.29
    End If
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Should the column remain 20 wide if the user clicks in another cell in the same column or should it snap back to width 6.29 if the user clicks away from row 8, even within the same column?

  3. #3
    New Lounger
    Join Date
    Aug 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791218' date='28-Aug-2009 16:53']Welcome to the Lounge!

    Should the column remain 20 wide if the user clicks in another cell in the same column or should it snap back to width 6.29 if the user clicks away from row 8, even within the same column?[/quote]

    If someone clicks off row 8 into another row in the same column then it should go back to the original 6.29. The column should only be wide when in row 8. Does that make sense?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a somewhat more efficient version of the code. It keeps track of the column of the previously active cell.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Static lngCol As Long
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      If lngCol >= 4 And lngCol <= 18 Then
    	Columns(lngCol).ColumnWidth = 6.29
      End If
      If ActiveCell.Column >= 4 And ActiveCell.Column <= 18 And ActiveCell.Row = 8 Then
    	ActiveCell.ColumnWidth = 20
      End If
      lngCol = ActiveCell.Column
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thumbs up

    [quote name='HansV' post='791222' date='28-Aug-2009 17:18']Here is a somewhat more efficient version of the code. It keeps track of the column of the previously active cell.

    [code

    Static lngCol As Long[/quote]
    Hello Hans

    I recall being chastised some many months ago for using the 'End' statement carelessly.

    Now I am surprised to find that the code you posted is considered to be running after the 'End Sub' statement. "Variables declared with the Static statement retain their values as long as the code is running."

    Is there any method of stopping the code, other than the 'End' command?
    Regards
    Don

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    [quote name='wdwells' post='791237' date='28-Aug-2009 17:48']Now I am surprised to find that the code you posted is considered to be running after the 'End Sub' statement. "Variables declared with the Static statement retain their values as long as the code is running."[/quote]
    Maybe "running" isn't the best word. The way I understand it, even after that procedure runs to completion, the value of a variable declared with Static will be available inside that procedure on future runs as long as the project (e.g., a template) remains in memory. Does that make sense?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As Jefferson wrote, using Static does not mean that the procedure* keeps on running. I means that the variable will remain in memory after the procedure has ended (=stopped running), until the project (workbook) containing the code is closed.

    Normally, all variables declared within a procedure are removed from memory when the procedure ends.

    One way to have a variable remain in memory is to declare it at the top of the module; the variable will then be available to all procedures within the module, or to all procedures within the project (workbook) if it has been declared as Public.

    Another way is to declare the variable within the procedure is Static. The variable will be available within the procedure only, whenever it runs.

    * Everything said about procedures in this post also applies to functions.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you both; Jefferson and Hans.
    Regards
    Don

  9. #9
    New Lounger
    Join Date
    Aug 2009
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791222' date='28-Aug-2009 17:18']Here is a somewhat more efficient version of the code. It keeps track of the column of the previously active cell.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Static lngCol As Long
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      If lngCol >= 4 And lngCol <= 18 Then
    	Columns(lngCol).ColumnWidth = 6.29
      End If
      If ActiveCell.Column >= 4 And ActiveCell.Column <= 18 And ActiveCell.Row = 8 Then
    	ActiveCell.ColumnWidth = 20
      End If
      lngCol = ActiveCell.Column
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End Sub
    [/quote]

    Thank you HansV!!! That works perfectly, and I would never have been able to figure that out on my own.!

Posting Permissions

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