stefanzman
New Member
- Joined
- Apr 26, 2020
- Messages
- 4
- Office Version
- 365
- 2019
- Platform
- Windows
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.
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
M11 is a pivot row header cell in a PivotTable with a “Classic” layout.
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