Excel macro not triggering on cell change

Question

I have a macro which previously triggered on a cell change but for some reason now it fails to trigger. I'm not sure what has changed to have caused this. All the macro has to do is refresh 4 pivots, which can be done manually no problem.

I've tried different ways of setting up a macro to trigger on a cell change, however they all fail to trigger on cell change.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range

    Set KeyCells = Range("V76:W76")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

    Sheets("NATFLOW").PivotTables("PivotTableA2").PivotCache.Refresh
    Sheets("NATFLOW").PivotTables("PivotTableB2").PivotCache.Refresh

    Sheets("NATTABLE").PivotTables("PivotTableAlpha2").PivotCache.Refresh
    Sheets("NATTABLE").PivotTables("PivotTableBeta2").PivotCache.Refresh

    End If

End Sub

The outcome is that nothing happens, including no error messages.

Your code seems like it should work fine. Is there any other code involved in your project? Reason I'm asking, there might have been a Application.EnableEvents = False applied. Try running a piece of code to turn this to True to see if your event triggers like it should. — JvdV
– JvdV, Commented Jun 3, 2019 at 10:30
By cell change you mean a cell updated by a formula? This event won't trigger in this case. — FAB
– FAB, Commented Jun 3, 2019 at 10:39
Thanks guys, it turns out it was VBA not installed on my system due to Windows having been re-installed while I was away. Sorry for the false alarm, I really appreciate your help. — Gabriel H
– Gabriel H, Commented Jun 3, 2019 at 11:03
@gabriel can I suggest you delete this question, since it's root cause is unrelated to anything mentioned in the Q. — chris neilsen
– chris neilsen, Commented Jun 5, 2019 at 10:15

Hippolyte BRINGER · Accepted Answer · 2019-06-03 10:37:16Z

1

If nothing happends, including no error messages, so is due to this Application.Intersect(KeyCells, Range(Target.Address)) equals nothing.

Try to run step by step and check if the value of Application.Intersect(KeyCells, Range(Target.Address))

answered Jun 3, 2019 at 10:37

Hippolyte BRINGER

8631 gold badge11 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabriel H Over a year ago

Sorry for the false alarm, it turns out VBA wasn't installed on my machine so macros weren't running. Your help much appreciated

Error 1004 · Accepted Answer · 2019-06-03 12:07:42Z

0

Try:

Option Explicit

'Method 1 - Specific sheet, specific objects
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("V76:W76")) Is Nothing And Target.Count = 1 Then

        With Sheets("NATFLOW")

            .PivotTables("PivotTableA2").PivotCache.Refresh
            .PivotTables("PivotTableB2").PivotCache.Refresh

            .PivotTables("PivotTableAlpha2").PivotCache.Refresh
            .PivotTables("PivotTableBeta2").PivotCache.Refresh

        End With

    End If

End Sub

'Method 2 - Refresh everyting from this workbook
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("V76:W76")) Is Nothing And Target.Count = 1 Then

        ThisWorkbook.RefreshAll

    End If

End Sub

edited Jun 3, 2019 at 12:07

answered Jun 3, 2019 at 12:02

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Excel macro not triggering on cell change

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related