VBA function fail w/Pivot tables

[stefanzman]

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

 

[stefanzman]

Sorry, more readable:

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

 

[stefanzman]

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

 

[stefanzman]

There is an ongoing thread on this at - Simple VBA function, pivot tables - OzGrid Free Excel/VBA Help Forum