Simple VBA function, pivot tables

Fluff - You didn't answer my questions:

Why does your code in post#9 just toggle between showing and hiding Sales Date? Is it because Target.Address seems to always be the full range of the pivot table in the sheet and seemingly never just an individual cell within the pivot table?

I'd still like to know the answer to this question too: 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)?

Also this one: Why is the condition that M11's value must be "Weeks" being completely ignored (which it definitely is in my orginal code and in the code you posted on post#9 since both of these snippets of code just toggle between showing and hiding Sales date regardless of M11's value).. 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")?

If I violated some rules of this forum/site I apologize - to clarify, how exactly did I violate these rules?

Ah I see. Thanks for helping me understand the rules in terms of cross-posting and supplying links. I'll definitely make sure to follow these rules going forward.

Thanks for all your help!

Quote from Fluff13

Can you please do that now, as per board rules.

So should I just provide links for every site where I've asked this question? Would this fix the rule violations?

If Not Application.Intersect(ValueCheck, Range(Target.Address)) Is Nothing And Range("M11").Value = "Weeks" Then

So the way I understand it now: the code above will run every time the pivot table refreshes, is filtered, etc as long M11 is weeks. Otherwise this will run:

ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then

This would mean, as long as M11 doesn't = "Weeks" Sales Date should remain hidden, though, right? Why does the code seem to toggle regardless of M11's value when attempting the code from post#1 and post#9?

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
 ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then
 With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date")
 .Orientation = xlRowField
 .Position = 2
 End With
 End If
 End If
 
 Application.ScreenUpdating = True
 Application.EnableEvents = True
 
End Sub

The code from post #2 just seems to hide Sales Date regardless of M11's value and it keeps it from being added back into the pivot table. Could this be because adding Sales Date back into the pivot table in position 2 among pivot table's "Rows" will shift "Weeks" back in to cell M11 (causing only the hiding portion of the if statement to run)? This would mean that the if statement might be running twice on every refresh. First, it identifies whether M11 is "Weeks" and hides Sales Date if this condition is satisfied. If not, it adds Sales Date back in shifting "Weeks" back in to M11, which causes the macro to run again, keeping it perpetually hidden. Does this sound right? Is there a workaround to fix this?

This is where my code stands now:

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

It seems to only hide the Sales Date pivot field once M12 becomes not empty and as long as M11 is "Weeks" (this means that the "Weeks" pivot field has been expanded). This is exactly what I want to happen. The problem now is that once M12 is empty again, Sales Date isn't being added back in automatically through the macro. I can add Sales Date back in manually, though, as long as I collapse the Sales Date field at the same time that I hide it (as the code above does) so that M11 remains empty when this pivot field gets added (meaning that "Weeks" is collapsed).

I have also posted to this same question to the following forum:

https://www.mrexcel.com/board/…l-w-pivot-tables.1132118/

Fluff - I totally understand. Thank you for all your assistance. I'm sorry if it's not completely clear what I would like to achieve. I've tried to articulate what I'm hoping to accomplish but it's totally possible that I haven't been able to do so very comprehensibly. If you know of anyone with experience triggering macros based on pivot field values (accounting for the possibility that a macro that runs twice - once when the document changes and second time once the macro changes the document), please direct them here if they don't mind trying to assist me.

Fluff - I may have figured this out. There seems some odd and unexpected behavior when monitoring cell status and contents when using Pivot Tables. The events that are fired and the associated timing / sequence when collapsing and restoring the pivots is a bit tricky.

I realize that is difficult to interpret or understand the exact purpose of the macro code, but it is all revolving around the unique requirements of customer involved and the way Pivot Tables deal with non-standard content.

I will send over an updated version of the code and worksheet shortly.