Simple VBA function, pivot tables

    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:


    Maybe

    My original code was toggling between showing and hiding Sales Date every time I refreshed data or made a change to a slicer or timeline, expanded or collapsed fields, etc. regardless of whether M11 was "Weeks" or M12's value had changed.


    Your code seems to just hide Sales Date and then prevent it from being added back in to the pivot table.


    What I want the code to do is toggle between showing and hiding Sales Date only when the value of M11 is weeks and the value of M12 changes.

    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:

    Code
    1. 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
    1. If Not Application.Intersect(ValueCheck, Range(Target.Address)) Is Nothing And Range("M11").Value = "Weeks" Then
    2. If ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlRowField Then
    3. ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden
    4. 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:

    Code
    1. ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then
    2. With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date")
    3. .Orientation = xlRowField
    4. .Position = 2
    5. End With

    Ok, how about

    Just seems to toggle between showing and hiding Sales Date like my original code did :/ I attached an example excel doc in one of my responses if you want to try playing around with the code within it to see the results of the code

    Your code would unhide the column if any cell on the sheet changed, not just M12.

    If the code in post#9 does not do what you want, then I'm afraid I simply don't understand your needs.

    If that's what my code currently does that's not what I intended.


    What I want the code to do is toggle between showing and hiding Sales Date when the value of cell M12 changes but only if the value of M11 is "Weeks".


    My code I pasted in to my original post just toggled between showing and hiding Sales Date when the workbook changed in any way (this is not how I intended the code to function so I'm obviously missing something or did something wrong..


    Your first change to my code seemed to just hide Sales Date and keep it from being added back into the pivot table.


    The code you pasted in post#9 just seems to toggle between showing and hiding Sales Date when the workbook changed in any way - just like the code I originally posted did (this is not how I intended the code to function so I'm obviously missing something or did something wrong..

    The difference between your code & post#9, is that your code would run if any cell was changed, the code from post#9 will only run if your change something in the pivot, which will affect M12.

    Other then that I'm afraid I don't know what to suggest, partly because I don't understand what it is you want.

    If you open the example file I attached in Post#6 and put your code into the "View Code" area of the "Event View" sheet you'll see that the code in post#9 toggles between showing and hiding Sales Date whether the value in M12 of the pivot table changes or not. So it just toggles the same way my original code did. You can observe this behavior by collapsing the "Years" row in the pivot table and then repeatedly refreshing the data. You'll see that M12's value remains blank but the Sales Date field toggles on every refresh anyway.

    I am fully aware of what my code does, and it is not the same as yours. With your code if you change ANY cell ANYWHERE in the sheet it will unhide the sales data.

    Whenever you refresh or change the pivot M12 will change & therefore trigger the macro. As i have already said I do not understand what you are trying to do.

    Fluff - I believe what he is saying that Sales Date should be hidden only if cell M11 contains the text "Weeks" when the macro is triggered. This is the test that is failing and not operating as desired.


    The question is not about what exact part of the sheet is changed to trigger the macro.

    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)?

    Actually, that does make sense, if the macro is simply triggered on a generic refresh / recalc. But it seems the cell content test would still be evaluated as true or false.

    ziceman - If M12 will always change on refresh (even if the value of this cell seems to stay blank) then that explains why the code toggles between showing hiding Sales Date on every refresh regardless of M12 value's when one clicks this cell. But it doesn't answer why the condition that M11's value must be "Weeks" is being completely ignored.. 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")?

    Once the Sales Date is Hidden then L11 is "Weeks" and not M11, therefore Sales Date will never unhide. As per you comments in post#3.


    Also I would suggest you read the rules, especially those regarding cross posting & supply all relevant links.