VBA to intersect cell changes (both data validation) and action separate functions

yhtomitl

New Member
Joined
Aug 6, 2019
Messages
3
Hi Everyone, on my worksheet I have two cells B3 and C3 which have data validation. So what I want to do is when either of the cells change, it will update two pivot table's filters. (Target & Actual)

Below is what I got working when looking at only B3 and updating the Region. The values within "Region Group" & "Region2" are the same, likewise "D Zone" & "Zone".

Code:
'Private Sub Worksheet_Change(ByVal Target As Range)
'
'    Dim PT As PivotTable
'    Dim PF As PivotField
'    Dim PT2 As PivotTable
'    Dim PF2 As PivotField
'    Dim Str As String
'    On Error Resume Next
'
'    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
'    Application.ScreenUpdating = False
'
'    Set PT = Worksheets("RetailsSPLive").PivotTables("Ret_Region")
'    Set PF = PT.PivotFields("Region Group")
'    Set PT2 = Worksheets("DB Target").PivotTables("DB_Target")
'    Set PF2 = PT2.PivotFields("Region2")
'    Str = Target.Text
'    PF.ClearAllFilters
'    PF.CurrentPage = Str
'    PF2.ClearAllFilters
'    PF2.CurrentPage = Str
'    Application.ScreenUpdating = True
'
'End Sub

However when I continue building upon it to also intersect C3, now neither cell change are being recognised.

Code:
    Dim PT As PivotTable
    Dim PF As PivotField
    Dim PT2 As PivotTable
    Dim PF2 As PivotField
    Dim Str As String
    On Error Resume Next
    
    If Intersect(Target, Range("C3")) Then 'Is Nothing Then
    Application.ScreenUpdating = False


    Set PT = Worksheets("RetailsSPLive").PivotTables("Ret_D")
    Set PF = PT.PivotFields("D Zone")
    Set PT2 = Worksheets("DB Target").PivotTables("DB_Target")
    Set PF2 = PT2.PivotFields("Zone")


        If Target.Text = "(All)" Then
        Str = Target.Text
        PF.ClearAllFilters
        PF2.ClearAllFilters
        Application.ScreenUpdating = True


        Else
        Str = Target.Text
        PF.ClearAllFilters
        PF.CurrentPage = Str
        PF2.ClearAllFilters
        PF2.CurrentPage = Str
        Application.ScreenUpdating = True
        End If




    ElseIf Intersect(Target, Range("B3")) Then 'Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    
    Set PT = Worksheets("RetailsSPLive").PivotTables("Ret_Region")
    Set PF = PT.PivotFields("Region Group")
    Set PT2 = Worksheets("DB Target").PivotTables("DB_Target")
    Set PF2 = PT2.PivotFields("Region2")
    Str = Target.Text
    PF.ClearAllFilters
    PF.CurrentPage = Str
    PF2.ClearAllFilters
    PF2.CurrentPage = Str
    Application.ScreenUpdating = True


    Else: Exit Sub
    End If
    
End Sub
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
It should be like
Code:
   If Not Intersect(Target, Range("C3")) Is Nothing Then
and the same for the elseif
 

yhtomitl

New Member
Joined
Aug 6, 2019
Messages
3
Hi Fluff,

Thank you! It's working now.

Still trying to get my head around why it's if not... is nothing
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
It's VBA's convoluted way of saying if it exists. You cannot test to see if the range exists, so you have to test if it doesn't.
in English rather than saying "If not x is nothing" we'd say "If x is not nothing"

As both those lines are checking a single cell rather than a range of cells you could also use
Code:
If Target.Address="$C$3" Then
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,528
Members
410,845
Latest member
OldSwimmer1650
Top