VBA function fail w/Pivot tables

stefanzman

New Member
Joined
Apr 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. 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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

stefanzman

New Member
Joined
Apr 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Sorry, more readable:

VBA 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

New Member
Joined
Apr 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

VBA Code:
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.:

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

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

Forum statistics

Threads
1,143,657
Messages
5,720,122
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top