VBA - Worksheet Calculate Event triggered when formula changes cell value

[logs10]

Hello,

I am trying to trigger a worksheet_calculate event that is triggered when a cell or range of cells are changed due to a formula changing the value. The code essentially takes the value of the changed cell (due to the cell changing by the formula) and then uses the new value for the Report Filter in a pivot table. The code is below, however, I am coming up with the Method 'Range' of object'_Worksheet' failed error. Any ideas on how I could make this work?

The pivot table and the cell being changed by a formula are located on the same worksheet.

Thanks in advance.

Private Sub Worksheet_Calculate()




If Worksheets("testdata").Range("L2").Value <> PrevVal Then
    PrevVal = Range("L2").Value
End If


'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String


'pivot data
Set pt = Worksheets("testdata").PivotTables("PivotTable2")
Set Field = pt.PivotFields("name")
NewCat = Worksheets("testdata").Range("L2").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With


End Sub

And the following in ThisWorkbook Object

Private Sub Workbook_Open()
    PrevVal = Worksheets("testdata").Range("L2").Value
End Sub

 

[WarPigl3t]

You entered this code into your macros through the Developer tab right? Well instead of doing that, look at the bottom of the excel program. Notice the sheet name tabs are there. Right click on the sheet that you are talking about. Now select "View Code" A similar macros area appears to the one that you opened using the Developer tab. Now before you paste your code in, you need to click on the right drop down list and select On_Change. This will insert an on change event handler into the code. It will replace the code you wrote that says Private Sub WorksheetCalculate(). Now just paste your code in there and you are done sir done.

 

[logs10]

Hi Warpiglet,

Thanks for the reply. No, I entered this code in the respective worksheet space. I tried using the change event you are talking about with still no luck. After doing some research, I believe it is supposed to be the calculate event like I originally had, however, I'm having a few issues getting the code to work.