How to trigger an event depending on a cell changing as a result of a formula

S

stakar

I have the following problem

Im using a pivot table and i change the value of the pivot table page

This pivot table's value updates a cell.
So i want each time the specific cell is updating with the pivo
table's value to update another pivot table.

Be more specific.

The cell's value depends on pivot table's cell value (B2) by using th
formula (=B2) in the cell F14.

So, if the pivot table = 1 then the cell's value will be 1
if the pivot table = 12 then the cell's value will be 12

I want to trigger an event each time the cell's value changes so t
update another table.

I found that code but its not running and it doesnt call the routin
'ResfreshWrc'

------------------------------------------------------------
Dim MonitorCell As Variant

Private Sub Worksheet_Activate()
MonitorCell = Cells(14, 6)
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Cells(14, 6) <> MonitorCell Then
RefreshWrc
MonitorCell = Cells(14, 6)
End If
End Sub

-----under Module---------------------------------------

Sub RefreshWrc()
ActiveSheet.PivotTables("WRC").RefreshTable
End Sub



****************************************
Thanks in Advance
Stathis
Patras, Greec
 
B

Bernie Deitrick

stakar,

Try using the calculate event instead, and using a cell as the storage of
the old value:

Dim MonitorCell As Range

Private Sub Worksheet_Calculate()
Set MonitorCell = Cells(15,6) ' use the cell F15 as a recording cell
If Cells(14, 6).Value <> MonitorCell.Value Then
RefreshWrc
MonitorCell.Value = Cells(14, 6).Value
End If
End Sub

HTH,
Bernie
MS Excel MVP
 
S

stakar

Bernie,
Thanks for the replay but there's a problem

When the coursor is on the 'RefreshWrc' refreshes the table 30 times a
least, consecutively.
It works like the pivot table's refresh button stucks and refreshin
the table consecutively.

Thanks in advance

***************
Stathi
 
B

Bernie Deitrick

Stathis,

You need to disable events when you do something that may force another
calculation. Try this version:

Private Sub Worksheet_Calculate()
Set MonitorCell = Cells(15,6) ' use the cell F15 as a recording cell
If Cells(14, 6).Value <> MonitorCell.Value Then
Application.EnableEvents = False
RefreshWrc
Application.EnableEvents = True
MonitorCell.Value = Cells(14, 6).Value
End If
End Sub

HTH,
Bernie
MS Excel MVP
 
S

stakar

I have another problem now

Because the cell data is depending on a pivot table on an othe
worksheet in the same workbook, when it has to read the RefreshWrc(
causes a problem because the RefreshWrc() is on another worksheet

Thats the code in module1
--------------------------------------
Sub RefreshWrc()
ActiveSheet.PivotTables("WRC").RefreshTable
End Sub
---------------------------------------

How can i solve the problem refreshing a pivot table on anothe
worksheet to refresh the WRC table too?

Mention that, this module it will be running after the first refresh
in its default worksheet !! The problem occurs when the refresh is no
in its default worksheet

Thanks in advance
*****************
Stathis
Patras-Greec
 
B

Bernie Deitrick

Stathis,

Try changing

ActiveSheet.P.....

to

Worksheets("SheetNameWithThePivotTable").P....

HTH,
Bernie
MS Excel MVP
 
S

stakar

Bernie said:
*Stathis,

Try changing

ActiveSheet.P.....

to

Worksheets("SheetNameWithThePivotTable").P....

HTH,
Bernie
MS Excel MVP
Click to expand...


Bernie,
It doesnt work

The main concept is how to refresh a pivot table which is in
worksheet "B" when i am running a vb code which using the workshhe
"A".

Thanks
****************
Stathis
Patras- Greec
 
B

Bernie Deitrick

Stathis,

We need to clear up terminology. When you say worksheets, do you mean a
separate workbook (with a different file name), or a separate tab
(worksheet) within a single workbook?

Bernie
 
S

stakar

Bernie,
Saying workbook i mean the xls file eg. c:\myname.xls
Saying worksheets i mean the tabs, the sheets in a single workbook
 
Top