Simple VBA function, pivot tables

  • Fluff - You didn't answer my questions:


    Why does your code in post#9 just toggle between showing and hiding Sales Date? Is it because Target.Address seems to always be the full range of the pivot table in the sheet and seemingly never just an individual cell within the pivot table?


    I'd still like to know the answer to this question too: 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)?


    Also this one: Why is the condition that M11's value must be "Weeks" being completely ignored (which it definitely is in my orginal code and in the code you posted on post#9 since both of these snippets of code just toggle between showing and hiding Sales date regardless of M11's value).. 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")?


    If I violated some rules of this forum/site I apologize - to clarify, how exactly did I violate these rules?

  • Is it because Target.Address seems to always be the full range of the pivot

    Only if something in the pivot changes.

    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?

    I've never used pivots, so could be wrong, but my understanding is that if you refresh the pivot, filter it, or do anything to it, then the entire pivot is considered to have changed.

    Could it be that excel is considering M11 to always be "Weeks"

    No, No, No, please read post#7 & 20.

    If I violated some rules of this forum/site I apologize - to clarify, how exactly did I violate these rules?

    You have asked this question on another site. This is called cross-posting and when you do that you are meant to supply link(s) to the other site(s)

  • Ah I see. Thanks for helping me understand the rules in terms of cross-posting and supplying links. I'll definitely follow these rules going forward.

    Can you please do that now, as per board rules.


    With your code you initially checking this

    Code
    1. If Not Application.Intersect(ValueCheck, Range(Target.Address)) Is Nothing And Range("M11").Value = "Weeks" Then

    If any part of that returns false, ie if M11 is not weeks, or you change any cell on the sheet, then the code will jump to this

    Code
    1. ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then

    All you are checking there is if the field is hidden.


    Also if you read the posts I just pointed to, or look at the sheet after you have hidden the sales date, you will see that M11 is not always weeks.

  • Can you please do that now, as per board rules.

    So should I just provide links for every site where I've asked this question? Would this fix the rule violations?

    Code
    1. If Not Application.Intersect(ValueCheck, Range(Target.Address)) Is Nothing And Range("M11").Value = "Weeks" Then

    So the way I understand it now: the code above will run every time the pivot table refreshes, is filtered, etc as long M11 is weeks. Otherwise this will run:

    Code
    1. ElseIf ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Date").Orientation = xlHidden Then

    This would mean, as long as M11 doesn't = "Weeks" Sales Date should remain hidden, though, right? Why does the code seem to toggle regardless of M11's value when attempting the code from post#1 and post#9?


    The code from post #2 just seems to hide Sales Date regardless of M11's value and it keeps it from being added back into the pivot table. Could this be because adding Sales Date back into the pivot table in position 2 among pivot table's "Rows" will shift "Weeks" back in to cell M11 (causing only the hiding portion of the if statement to run)? This would mean that the if statement might be running twice on every refresh. First, it identifies whether M11 is "Weeks" and hides Sales Date if this condition is satisfied. If not, it adds Sales Date back in shifting "Weeks" back in to M11, which causes the macro to run again, keeping it perpetually hidden. Does this sound right? Is there a workaround to fix this?

  • This is where my code stands now:



    It seems to only hide the Sales Date pivot field once M12 becomes not empty and as long as M11 is "Weeks" (this means that the "Weeks" pivot field has been expanded). This is exactly what I want to happen. The problem now is that once M12 is empty again, Sales Date isn't being added back in automatically through the macro. I can add Sales Date back in manually, though, as long as I collapse the Sales Date field at the same time that I hide it (as the code above does) so that M11 remains empty when this pivot field gets added (meaning that "Weeks" is collapsed).

  • Fluff - I totally understand. Thank you for all your assistance. I'm sorry if it's not completely clear what I would like to achieve. I've tried to articulate what I'm hoping to accomplish but it's totally possible that I haven't been able to do so very comprehensibly. If you know of anyone with experience triggering macros based on pivot field values (accounting for the possibility that a macro that runs twice - once when the document changes and second time once the macro changes the document), please direct them here if they don't mind trying to assist me.

  • Fluff - I may have figured this out. There seems some odd and unexpected behavior when monitoring cell status and contents when using Pivot Tables. The events that are fired and the associated timing / sequence when collapsing and restoring the pivots is a bit tricky.

    I realize that is difficult to interpret or understand the exact purpose of the macro code, but it is all revolving around the unique requirements of customer involved and the way Pivot Tables deal with non-standard content.

    I will send over an updated version of the code and worksheet shortly.