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:
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