Can anyone stop this running all the time?

DUI

Board Regular
Joined
Jul 3, 2006
Messages
109
Hi Guys,

The following code seems to run fairly well... but is there anyway to make it run only when the relevant cells are used. (you can see the curser think every time you move the active cell)


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False


'Expand Trade Columns
If Target.Column = 24 Or Target.Column = 25 Or Target.Column = 26 Then
Columns("Y:Z").EntireColumn.Hidden = False
Else: Columns("Y:Z").EntireColumn.Hidden = True
End If


Application.ScreenUpdating = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


If Target.Address = "$H$13" Or Target.Address = "$K$13" Then
Range("A13:A400").AutoFilter Field:=1, Criteria1:="1"
End If


Application.ScreenUpdating = True

End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
This might be a bit better:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'Expand Trade Columns
   Select Case Target.Column
      Case 24 To 26
         If Columns("Y:Z").EntireColumn.Hidden = True Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = False
            Application.ScreenUpdating = True
         End If
      Case Else
         If Columns("Y:Z").EntireColumn.Hidden = False Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = True
            Application.ScreenUpdating = True
         End If
   End Select
End Sub
 
Upvote 0
This might be a bit better:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   'Expand Trade Columns
   Select Case Target.Column
      Case 24 To 26
         If Columns("Y:Z").EntireColumn.Hidden = True Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = False
            Application.ScreenUpdating = True
         End If
      Case Else
         If Columns("Y:Z").EntireColumn.Hidden = False Then
            Application.ScreenUpdating = False
            Columns("Y:Z").EntireColumn.Hidden = True
            Application.ScreenUpdating = True
         End If
   End Select
End Sub

Your a GENIUS!.. I don't know why it works, but it does... It now doesn't think everytime I make a move. Thank You.
 
Upvote 0
It works by not trying to rehide the columns if they're already hidden and not turning screenupdating on and off unnecessarily. That helps to avoid the flicker.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top