VBA script for Worksheet Change has stopped working

ubermensch

New Member
Joined
Jun 24, 2011
Messages
6
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.

Code:
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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.

Code:
Call multiFindandReplace

Msgbox "I'm back"


Application.EnableEvents = True
Application.ScreenUpdating = True

Howard
 

ubermensch

New Member
Joined
Jun 24, 2011
Messages
6
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.

Code:
Call multiFindandReplace

Msgbox "I'm back"


Application.EnableEvents = True
Application.ScreenUpdating = True

Howard
 

ubermensch

New Member
Joined
Jun 24, 2011
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top