Simple VBA function, pivot tables

The question I have is why this code simply toggles between showing and hiding the Sales Date pivot field. It’s only supposed to hide the Sales Date pivot field when the cell M11’s value is “Weeks” but it seems like it’s ignoring this second condition of the if statement. M11 is a pivot row header cell in a PivotTable with a “Classic” layout.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ValueCheck As Range
 
    Set ValueCheck = Range("M12")
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
 
If Not Application.Intersect(ValueCheck, Range(Target.Address)) Is Nothing And Range("M11").Value = "Weeks" Then
    If ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlRowField Then
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden
    End If
ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date")
        .Orientation = xlRowField
        .Position = 2
    End With
   
Else
End If
 
   Application.ScreenUpdating = True
   Application.EnableEvents = True
 
End Sub

Hey Fluff13,

Thanks for your reply.

Unfortunately, your code just seems to hide the Sales Date Pivotfield and prevents this Pivotfield from being added back into the pivot table..

My original code was toggling between showing and hiding Sales Date every time I refreshed data or made a change to a slicer or timeline, expanded or collapsed fields, etc. regardless of whether M11 was "Weeks" or M12's value had changed.

Your code seems to just hide Sales Date and then prevent it from being added back in to the pivot table.

What I want the code to do is toggle between showing and hiding Sales Date only when the value of M11 is weeks and the value of M12 changes.

Here's the file so that you can see what's happening.

My code might not follow the logic I intend it to. This is how I intended my code to work:

Trigger VBA when worksheet changes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range in variable "ValueCheck" changes and M11 = "Weeks" then if Sales Date isn't hidden hide it.:

If Not Application.Intersect(ValueCheck, Range(Target.Address)) Is Nothing And Range("M11").Value = "Weeks" Then
 If ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlRowField Then
 ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden
 End If

If Range in variable "ValueCheck" changes and M11 <> "Weeks" then if Sales Date is hidden then show it and put in the 2nd row position:

ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then
 With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date")
 .Orientation = xlRowField
 .Position = 2
 End With

Just seems to toggle between showing and hiding Sales Date like my original code did I attached an example excel doc in one of my responses if you want to try playing around with the code within it to see the results of the code

If that's what my code currently does that's not what I intended.

What I want the code to do is toggle between showing and hiding Sales Date when the value of cell M12 changes but only if the value of M11 is "Weeks".

My code I pasted in to my original post just toggled between showing and hiding Sales Date when the workbook changed in any way (this is not how I intended the code to function so I'm obviously missing something or did something wrong..

Your first change to my code seemed to just hide Sales Date and keep it from being added back into the pivot table.

The code you pasted in post#9 just seems to toggle between showing and hiding Sales Date when the workbook changed in any way - just like the code I originally posted did (this is not how I intended the code to function so I'm obviously missing something or did something wrong..

If you open the example file I attached in Post#6 and put your code into the "View Code" area of the "Event View" sheet you'll see that the code in post#9 toggles between showing and hiding Sales Date whether the value in M12 of the pivot table changes or not. So it just toggles the same way my original code did. You can observe this behavior by collapsing the "Years" row in the pivot table and then repeatedly refreshing the data. You'll see that M12's value remains blank but the Sales Date field toggles on every refresh anyway.

So are you saying that excel will conclude that cell M12 has changed when you refresh data despite the value of M12 seemingly remaining blank? Is there any way to hide Sales Channel only when the value of M12 changes? Is M12 changing even if its value shows as blank (when I click on this cell with the Years pivot row collapsed)?

ziceman - If M12 will always change on refresh (even if the value of this cell seems to stay blank) then that explains why the code toggles between showing hiding Sales Date on every refresh regardless of M12 value's when one clicks this cell. But it doesn't answer why the condition that M11's value must be "Weeks" is being completely ignored.. Could it be that excel is considering M11 to always be "Weeks" (perhaps by being triggered when M11 is "Weeks" AND when M11's value changes to "Weeks")?