Change event not calling macro Excel Vba

Question

I have a range of values in a column which I am targeting.
Once any of the values in that range changes, it is supposed to call a macro. The macro I am calling works but my change event is not getting recognized. Is there something wrong with my code?

My change even macro is located on the sheet where the change event is happening:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$H$5:$H$32" Then
        Call SetAxes
    End If
End Sub

Do I need to be more specific with the range address?

Worksheet_calculate does not have ByVal Target As Range and you cannot just add it expecting it to work. — Scott Craner
– Scott Craner, Commented May 16, 2018 at 20:51
Worksheet change does not capture changes due to formulas. You will need to save the values in another location then compare the values after calc, if different then save the new values in the other location and run the sub — Scott Craner
– Scott Craner, Commented May 16, 2018 at 20:55
So the question is, "Are these cells being changed by formula or by manual input directly in the cells?" — Scott Craner
– Scott Craner, Commented May 16, 2018 at 20:58
@ScottCraner Thanks. I changed to back to Worksheet_Calculate and simply called the macro and it works. — Remi
– Remi, Commented May 16, 2018 at 20:58

barrowc · Accepted Answer · 2018-05-16 22:52:55Z

1

Target.Address = "$H$5:$H$32" will only be True if that entire range changes at once.

To trap changes to one or more cells within that range, use Application.Intersect like this:

If Not Application.Intersect(Target, Range("$H$5:$H$32")) Is Nothing Then

answered May 16, 2018 at 22:52

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

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

Comments

Remi · Accepted Answer · 2018-05-16 21:00:40Z

0

I simply used worksheet_calculate and called the function when anything changes on the worksheet.
All the macro it is calling does is set the axes, so I don't mind if it runs repeatedly.

Private Sub Worksheet_Calculate()

        Call SetAxes

End Sub

answered May 16, 2018 at 21:00

Remi

1693 silver badges15 bronze badges

Comments

EvR · Accepted Answer · 2018-05-16 21:28:39Z

0

as a workaround, you could add a function:

Function FakeChange() As Integer
SetAxes
FakeChange = 0
End Function

and add behind your formulas in "$H$5:$H$32" +Fakechange() like: ='your formula'+ FakeChange()

This function will be triggered when the results of your formulas are changed

answered May 16, 2018 at 21:28

EvR

3,5082 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

Change event not calling macro Excel Vba

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related