Update a pivot chart from a slicer change to a table

[phrankndonna]

Hi. I've got a data table (Table1) and a slicer. On another sheet, I have a pivot table and chart. I'd like to be able to trigger the pivot chart update when the slicer gets changed. I've tried the code below (which works as a standalone module) in a worksheet_change event, and that didn't work. From reviewing a plethora of posts, it appears that a slicer change can't trigger this event. So I tried adding a sub-total formula to cell $Z$1 on the worksheet and tried the Worksheet_SelectionChange event, and that also didn't work. I can sort of get my head around why the first one didn't work, but why wouldn't the Worksheet_SelectionChange event see the change in cell Z1? Is there any way to get this to trigger the event? Thanks for looking.

Please Login or Register  to view this content.

[frabulator]

have you tried saving the workbook and reopening it? Sometimes the workbook/worksheet triggers do not load after they have been changed unless they are present when the workbook first opens.

[phrankndonna]

Yes, I tried that. Both events trigger if I update cell Z1 manually, but not if it gets updated via the Slicer change.

[frabulator]

Try to use the following trigger. I am not familiar enough with pivot charts to say if it works exactly like I think it should, but I think this is the trigger that is fired when a pivot chart/slicer is changed or updated.

Code pulled from here.

Please Login or Register  to view this content.

If you are wanting the macro to fire with the slicer, you could do a workaround that says when the slider is moved have Z1 become selected. Once Z1 is selected, your chart should update, then select the previous selected cell. If you contain this inside of a Application.ScreenUpdating = False then the user will never see it.

However, there are other declarations I found, just looking around inside of the Worksheet VBA section. You could try your hand with some of these and see what happens when?

Please Login or Register  to view this content.

[frabulator]

After sleeping on it I think I understand now. You want the chart to update when the value changes in cell Z1, correct?

Currently, your code only checks if the cell Z1 is selected, THEN it updates the cell. Selecting Z1 and changing the value are two different things.

To check to see if a value has changed, you can set the value to a public string. If that string matches the previous string, then do nothing. If it does not, then update.

Here is an example:

Please Login or Register  to view this content.

[phrankndonna]

Hi. Thanks for looking at this. I tried what you suggested, and it's still not triggering the event when I change the Slicer. I did sort of a work around that isn't ideal, but it works. In the email sub, I added the code below. Since that's in a regular sub and actually changes the value, it triggers the worksheet_change event, when changes the pivot table/chart. Thank you for taking the time to help!

myZvalue = Range("Z1").Value
If myZvalue = 1 Then
Range("Z1").Value = 2
Else
If myZvalue = 2 Then
Range("Z1").Value = 1
End If
End If