Worksheet Change Event

[one3nine0]

I have a cell that is a formula based off pivot table. The value will change with slicers that the user can select from.

How do I have the workbook refresh every time the value of the cell changes?

What I have so far is:

In Sheet1 object:
Private Sub Worksheet_Calculate()
If Range("E7").Value <> PrevVal Then
ActiveWorkbook.RefreshAll
End If
End Sub

But how do I make PrevVal equal the previous value of the cell before the change? or how do I make it so that the workbook will refresh once E7 changes?

 

[Dendro]

Hi,

Use this in the worksheet macro to trigger on on a change in the sheet:

<code>Private Sub Worksheet_Change(ByVal Target As Range)</code>

Next use this code to only trigger when E7 is changed:

If Target = Range("E7") Then
    ActiveWorkbook.RefreshAll
End If

 

[one3nine0]

Hi,

Use this in the worksheet macro to trigger on on a change in the sheet:

<code>Private Sub Worksheet_Change(ByVal Target As Range)</code>

Next use this code to only trigger when E7 is changed:

If Target = Range("E7") Then
    ActiveWorkbook.RefreshAll
End If

Hey Dendro, unfortunately, because E7 contains a formula, this doesn't work. The formula doesn't change which in turn doesn't trigger the event. Only the value of the formula result changes, and I am not sure how to trigger the event for that instance.

 

[pjmorris]

For what its worth, there must be a cell thats changed to make the pivot table change, so perhaps the answer is to trigger the event when that occurs and then force an update to the pivottable as part of the macro.

Regards

 

[one3nine0]

For what its worth, there must be a cell thats changed to make the pivot table change, so perhaps the answer is to trigger the event when that occurs and then force an update to the pivottable as part of the macro.

Regards

So I am using powerpivot and slicers to change the tables, so unless I can target a portion of a pivot table or when the slicer is changed, then I dont think that would work.

 

[pjmorris]

Oh, sorry, I dont have any experience with either powerpivot or slicers. Though I would have anticipated some way of trapping the action of a slicer - perhaps if you record a macro as you adjust the slicer you'll get some insight that will be useful.

Good luck.