Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Controls bypassing Worksheet_Change? (2000)

    Hi All,

    I have some code that formats the contents of a pivot table by color, to reflect each value's magnitude with respect to the whole table (that is, I make a color map). I would like to refresh the color mapping when the user changes one of the page field selections, but changing the value through the pivot table drop-down seems to bypass the Worksheet_Change event.

    For example, the page field drop-down lists (All), 5, 10 and 15. If I select a different choice in the drop-down, the WorkSheet_Change event does not respond. If I *manually* enter 5 or 10, or whatnot into the corresponding cell, the event is triggered.

    Is there an event associated with changing a pivot table field? Or does anyone know another way of detecting automatically that a pivor field selection was changed?

    Thanks!

    JIM

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Pivot Table Controls bypassing Worksheet_Change? (2000)

    I created a test pivot table and used conditional formatting to color code the results acording to value.
    When I used the page field dropdown, the pivot table was refreshed and the color coded results updated.
    To do this, you must have the Pivot table-Options-Preserve formatting checkbox selected on.

    Does this help?

    zeddy

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Controls bypassing Worksheet_Change? (2000)

    I think the
    <pre>Worksheet_Calculate</pre>

    event is triggered when a pivot table page is changed
    Steve

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Controls bypassing Worksheet_Change? (2000)

    Zeddy - You are correct! However, my coloring scheme is more complex than can be handled by conditional formatting.
    sdckapr - You are also correct (I think, haven't tested it)! However, I would like to limit the recoloring to only when a page field is changed, not each time the sheet is recalculated (Worksheet_Calculate happens under lots of conditions, and doesn't have a Target argument to distinguish a cell or range).

    I think I will put a button near the page field selector, then instruct the user to click it for updating the color map.

    Thanks for responding!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Controls bypassing Worksheet_Change? (2000)

    Just a thought (don't know how practical):
    You could put a combobox OVER the page field (essentially hiding it) that essentially LOOKS like the page field and is filled with the same items

    When the user chooses an item from this combo box, the macro could fill in the page field value and then run your code to calculate.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •