Simplify intersects in Worksheet_Change Routine

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
Is there a simpler way you achieve this exclusion of cells? (Cell contents vary)
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("A5:AF180")) Is Nothing Or _
    Not Intersect(Target, Range("U8")) Is Nothing Or _
    Not Intersect(Target, Range("X8")) Is Nothing Or _
    Not Intersect(Target, Range("AA8")) Is Nothing Or _
    Not Intersect(Target, Range("AD8")) Is Nothing Or _
    Not Intersect(Target, Range("U32")) Is Nothing Or _
    Not Intersect(Target, Range("X32")) Is Nothing Or _
    Not Intersect(Target, Range("AA32")) Is Nothing Or _
    Not Intersect(Target, Range("AD32")) Is Nothing Or _
    Not Intersect(Target, Range("U56")) Is Nothing Or _
    Not Intersect(Target, Range("X56")) Is Nothing Or _
    Not Intersect(Target, Range("AA56")) Is Nothing Or _
    Not Intersect(Target, Range("AD56")) Is Nothing Or _
    Not Intersect(Target, Range("U101")) Is Nothing Or _
    Not Intersect(Target, Range("X101")) Is Nothing Or _
    Not Intersect(Target, Range("AA101")) Is Nothing Or _
    Not Intersect(Target, Range("AD101")) Is Nothing Or _
    Not Intersect(Target, Range("U129")) Is Nothing Or _
    Not Intersect(Target, Range("X129")) Is Nothing Or _
    Not Intersect(Target, Range("AA129")) Is Nothing Or _
    Not Intersect(Target, Range("AD129")) Is Nothing _
    Then Exit Sub

Cheers Paul
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,563
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I think your logic is wrong there as that wouldn't work for any cells at all. You could use:

Code:
If Intersect(Target, Range("A5:AF180,U8,X8,AA8,AD8,U32,X32,AA32,AD32,U56,X56,AA56,AD56,U101,X101,AA101,AD101,U129,X129,AA129,AD129")) Is Nothing Then Exit Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,052
Office Version
  1. 365
Platform
  1. Windows
How about like
VBA Code:
    If Not Intersect(Target, Range("A5:AF180")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "U8", "X8", "AA8"
            Exit Sub
      End Select
      MsgBox 2
   End If
 

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
I think your logic is wrong there as that wouldn't work for any cells at all. You could use:

Code:
If Intersect(Target, Range("A5:AF180,U8,X8,AA8,AD8,U32,X32,AA32,AD32,U56,X56,AA56,AD56,U101,X101,AA101,AD101,U129,X129,AA129,AD129")) Is Nothing Then Exit Sub
 

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi and Thanks for replys
First line sets the area that triggers a change eg A4 chnage does not but A5 does
VBA Code:
    If Intersect(Target, Range("A5:AF180")) Is Nothing Or _
The remaining lines which i can condence using the example are cells that do not trigger a change
Code:
Not Intersect(Target, Range("U8")) Is Nothing Or _
    Not Intersect(Target, Range("X8")) Is Nothing Or _
    Not Intersect(Target, Range("AA8")) Is Nothing Or _

    etc

So it does work but just very clumsy coding
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,563
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That is not logical. If you change A5, then you didn't change U8 so that part would be True, and the Or would evaluate to True, and the code would exit. Why are you adding in cells you don't want to trigger the change?
 

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

That is not logical. If you change A5, then you didn't change U8 so that part would be True, and the Or would evaluate to True, and the code would exit. Why are you adding in cells you don't want to trigger the change?
Ye you are right but i have another sub that rewrites formula cells if inputs cells to those formula cells contain values. My apologies I neglected to mention that .

VBA Code:
Sub CalucatedValuesInInputs()

    Set Metric = Sheet9.Range("I103")
    For RowVal = 5 To 1001
        If Sheet3.Cells(RowVal, 5) = Metric Then
            For ColVal = 21 To 30 Step 3
                If Sheet3.Cells(RowVal + 1, ColVal) = "" And Sheet3.Cells(RowVal + 2, ColVal) = "" Then
                    Sheet3.Cells(RowVal, ColVal) = Sheet10.Range("$C$78")
                Else
                    Sheet3.Cells(RowVal, ColVal) = Sheet3.Cells(RowVal + 1, ColVal) + Sheet3.Cells(RowVal + 2, ColVal)
                End If
            Next
        End If
    Next

End Sub

This rewites the formulas in those cells when the sheet is deactivated and that is why i needed to exclude them in the _Change sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,563
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you don't want that to trigger the Change event, just disable events when you run that code.
 

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
If you don't want that to trigger the Change event, just disable events when you run that code.
Sometimes the blindingly obvious is not always seen. Many thanks and of course disabling events on that sub call worked and all NOT statements in original removed as unnecessary.
Many thanks, and that why good help is always here if you ask the right question and give all info :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,563
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sometimes you can't see the forest for the trees when it's your own project! :) Glad we could help.
 

Forum statistics

Threads
1,144,524
Messages
5,724,842
Members
422,585
Latest member
k3n

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
Top