How to fix problem of Worksheet_Pivottableupdate event?

[feipezi]

Hi,

I tried to use the event to update or change the CurrentPage of a PT. As I did that, the pt went crazy like ran into a endless loop, with frequent flash of the screen. As I hit ESC, and checked Debug, I got a 1004: Unable to set number format property of the PivotField class, things like that.
This is not the first time that happened to me.

Thanks in advance.

Here is the Code.


Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call TcShare
Call TRx
End Sub

Sub TRx()
For i = 1 To 6
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of trx" & i)
.Calculation = xlNormal
.NumberFormat = "#,##0"
End With
Next
End Sub

Sub TcShare()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable1")
pt.PivotFields("Mkt").CurrentPage = CStr(Range("M2").Value)
End Sub

 

[combo]

Check if called pivot fields exist. Switch off events when the table is changed by code, otherwise changes call the PivotTableUpdate event again. You could make use of pivot table object returned by the event procedure (Target):

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
Call TcShare(Target)
Call TRx(Target)
Application.EnableEvents = True
End Sub

Sub TRx(pt As PivotTable)
For i = 1 To 6
    With pt.PivotFields("Sum of trx" & i)
        .Calculation = xlNormal
        .NumberFormat = "#,##0"
    End With
Next
End Sub

Sub TcShare(pt As PivotTable)
pt.PivotFields("Mkt").CurrentPage = CStr(Range("M2").Value)
End Sub

combo

 

[feipezi]

Hi Combo,

Thank you very much for your response.

It really works. With Application.ScreenUpdating=False/True, the crazy flash is gone too.

But I'm still quite confused about using Application.EnableEvents = False/True. Sometimes, I have to reboot the computer before a event works; even I use ...True/True, still not working, unless rebooting the machine. It's hard to believe, isn't it?

Thanks again.

 

[combo]

What's the problem? If Application.EnableEvents=Falset, the given excel instance does not fire event procedures of excel components (it does not have impact to userform or activex controls evens).

Roboot needed: are macros enabled? Do you use 'On Error...' statement (with skiping part of code due to error)? Global variables that can be reset sometimes? Try to debug code from the beginning of event procedure (the ScreenUpdating does not matter, however the screen does not refresh).

combo

 

[SkipVought]

If your code executes...

Application.EnableEvents = False

and THEN you either ABORT or EXIT a procedure before the...

Application.EnableEvents = True

executes, EVENTS will be DISABLED.

No need to REBOOT! Just execute...

Application.EnableEvents = True

ANYWHERE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Combo & Skip,

Thank you both for the info, like always.

I'll try and get back to you if I have problems.

Thanks again.

 

[feipezi]

Hi,
I came back again. You can see the following code is simple enough, as a matter of fact, you cannot make it simpler than this: no calling macros and any other tricks. Guess what, nothing happens as I change the PageField or other pivottable fields.

In fact, I don't want to reboot. It's too much. But after rebooting, it will work. What's going on?

Thanks in advance.



Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
MsgBox "Hello."
Application.EnableEvents = True
End Sub

 

[combo]

Debug the code: add a breakpoint at "Application.EnableEvents = False". If the code stops there, execute line by line. If not, execute "Application.EnableEvents = True" in the Immediate window and try again.
No other event codes in worksheet's module (Worksheet_Change event etc.)?

combo

 

[feipezi]

No, Combo. I don't have any other events on the same sheet. But as I close the session, I mean Excel, and start over again, then it will work. Can I do anything to solve the problem without shutting down the application?

Thanks.

 

[combo]

Any add-ins? Please do the two things from my previous post and tell what's going on.

combo