Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change or Worksheet_calculate
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change or Worksheet_calculate
Hi
The Worksheet_calculate event does not have a Target object, which is what your existing code requires. HTH. Best wishes Harald "karaeloko" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change or Worksheet_calculate
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_change or Worksheet_calculate
On Aug 6, 7:33*pm, karaeloko wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change vs Worksheet_Calculate | Excel Programming | |||
Re : Excel Worksheet_Calculate() & Worksheet_Change() | Excel Programming | |||
Worksheet_Calculate or Worksheet_Change? | Excel Programming | |||
Nesting worksheet_Calculate inside worksheet_CHANGE | Excel Programming |