Posts by stefanzman

  • Simple VBA function, pivot tables

    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.

  • Simple VBA function, pivot tables

    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.

  • Simple VBA function, pivot tables

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

  • Simple VBA function, pivot tables

    Quote from Fluff13

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

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

  • Simple VBA function, pivot tables

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

  • Simple VBA function, pivot tables

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

  • Simple VBA function, pivot tables

    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.

  • Simple VBA function, pivot tables

    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..

  • Simple VBA function, pivot tables

    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

  • Simple VBA function, pivot tables

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

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

  • Simple VBA function, pivot tables

    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.

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