Worksheet_change or Worksheet_calculate

[karaeloko]

Hi,

I have code to change the field values in all pivot tables on Sheet1
based on the values entered in some specific cells. On cell A2 I have
a formula and I need the code to be triggered when it recalculates so
it will change the pivot table field value. The problem is that the
code I have works perfectly when the value is manually entered but not
when it changes based on a formula. Also tried the
Worksheet_Calculate () event but I don't know how to prevent the
"Object Required" error.

Would greatly appreciate the help!!

Code is pasted below:

Private Sub Worksheet_change(ByVal target As Range)
Dim a(10, 2) As Integer
Dim fieldadd(10, 2) As String
fieldadd(1, 1) = "$A$2"
fieldadd(1, 2) = "Name"
Application.ScreenUpdating = False
If (target.Address) = fieldadd(1, 1) Then
For Each pt In ActiveSheet.PivotTables
For Each pt1 In ActiveSheet.PivotTables
pt1.PivotFields(fieldadd(1, 2)). _
CurrentPage = target.Value
Next pt1
On Error Resume Next
Next pt
End If
target.Select
Application.ScreenUpdating = True
End Sub

 

[Harald Staff]

Hi

The Worksheet_calculate event does not have a Target object, which is what
your existing code requires.

HTH. Best wishes Harald

 

[karaeloko]

Hello Harald,

Yes you are right. The problem is that I don't know how to fix it and
that is the reason why I posted it here to see if someone might help.

I found the code I'm using on the internet and as I mentioned it works
perfectly when the values are manually entered in the cell but not if
there is a formula that changes based on another cell.

regards,

 

[Don Guillett Excel MVP]

Hello Harald,

Yes you are right. The problem is that I don't know how to fix it and
that is the reason why I posted it here to see if someone might help.

I found the code I'm using on the internet and as I mentioned it works
perfectly when the values are manually entered in the cell but not if
there is a formula that changes based on another cell.

regards,

Modify to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Address
Case Is = Range("a1").Address, Range("c3").Address
MsgBox "Hi"
Case Else
End Select
End Sub