• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table Refresh Question

R

rjacmuto32

Member
Can a pivot table be updated via code whenever a cell changes a value but the value is not changed by any button pushing or click event?


I've utilized the Hui rolloever technique for charting from the chadoo site with the Hyperlinks to change the charts. I built extra steps off that hyperlink cells with some additonal offset and index match formula's. One of those formula's puts a value into cell AB4. All by a hover over technique. I need a way to update my pivot table whever the value in cell AB4 is changed.
 
Sure thing. Assuming that EnableEvents hasn't been turned off, you can probably use the worksheet_calculate event, like this:

[pre]
Code: 
Private Sub Worksheet_Calculate()
Application.EnableEvents = False 'Need to turn this off so we
'don't get caught in an endless loop
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh
Application.EnableEvents = True
End Sub
[/pre]
Obviously, you'll need to change the sheet name and name of PivotTable to match your exact setup.
 
How do I call that macro to run?


If cell AB4 changes from say 1 to 3, I want to do that pivot table refresh line code.


Then later if cell AB4 changes from 3 to 17 same thing, refresh the pivot table.


Anyway to do that without a trigger?
 
If AB4 is being changed by user or a macro, you can do this way:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("AB4")) Is Nothing Then Exit Sub
Application.EnableEvents = False 'Need to turn this off so we
'don't get caught in an endless loop
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh
Application.EnableEvents = True

End Sub
If it's simply the result of a formula that's changing, you can either use this trigger or just have a plain macro that you run when you know the cell will change:

Sub RefreshTable()
Application.EnableEvents = False 'Need to turn this off so we
'don't get caught in an endless loop
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh
Application.EnableEvents = True
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top