VBA script for Worksheet Change has stopped working

[ubermensch]

I recently had to replace my hard drive and in the process I ended up going from Excel 2010 to Excel 2007. The script has been working fine previously but now it has stopped. I have checked the Trust Centre settings to make sure macros will run. I have also checked ?Application.EnableEvents and that returns "true". Any tips on what other things I can check would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String


strField = "Account Number"


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False


    If Target.Address = Range("C2").Address Then
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
            EnableMultiplePageItems = False
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If


Call multiFindandReplace


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

 

[L. Howard]

Your code calls the macro multiFindandReplace. It is possible there is an Exit Sub condition in that macro, whereupon I believe the focus would NOT come back to the next line of code in the "calling" macro, which is the .EnableEvents and .ScreenUpdating.

You could put a msgbox in to test if the focus is returning to the Change_Event macro.

Call multiFindandReplace

Msgbox "I'm back"


Application.EnableEvents = True
Application.ScreenUpdating = True

Howard

 

[ubermensch]

Hi Howard,

Thanks for your response. I tried your suggestion and confirmed that the focus was coming back on the "calling" macro. I found out that one of the pivot table filter values was getting updated but the other pivot table on the same worksheet was no.

To resolve I ended up recreating the pivot table that wasn't getting updated and now it's working OK. Rather than a script problem it looks like it might have been a glitchy pivot table.

Cheers,

UM

Your code calls the macro multiFindandReplace. It is possible there is an Exit Sub condition in that macro, whereupon I believe the focus would NOT come back to the next line of code in the "calling" macro, which is the .EnableEvents and .ScreenUpdating.

You could put a msgbox in to test if the focus is returning to the Change_Event macro.

Call multiFindandReplace

Msgbox "I'm back"


Application.EnableEvents = True
Application.ScreenUpdating = True

Howard

 

[ubermensch]

Update: Found the root cause. The pivot table that was not updating was because the value that was selected in the filter would have selected too many rows and overlapped a second pivot table. Because I am using code to change the filter value I assume I am not getting the usual error message about overlapping tables and the table just doesn't update. I added a bunch more rows between the tables and the issue was fixed.

Hi Howard,

Thanks for your response. I tried your suggestion and confirmed that the focus was coming back on the "calling" macro. I found out that one of the pivot table filter values was getting updated but the other pivot table on the same worksheet was no.

To resolve I ended up recreating the pivot table that wasn't getting updated and now it's working OK. Rather than a script problem it looks like it might have been a glitchy pivot table.

Cheers,

UM