How to call a Macro when a named range (cell) is changed

Question

I am trying to call a macro named "RE_environmental" when the cell named "RE_1" is changed (i.e. they mark a X in the cell). I've tried several different variations of codes including these two and nothing is happening:

[The first code does work if I use the exact cell location and not the named cell. --> $E$62]

     Private Sub Worksheet_Change(ByVal Target As Range)

     If Target.Address = "RE_1" Then

     Call RE_environmental

     End If

     End Sub

AND

     Private Sub Worksheet_Change(ByVal Target As Range)

     If Range("Name").Select = "RE_1" Then

     Call RE_environmental

     End If

     End Sub

--Thanks in Advanced and please let me know if you need more information.

score 1 · Answer 1 · answered Apr 01 '14 at 17:22

1

If Target is always single-cell range, you can use this one:

If Target.Address = Range("RE_1").Address Then
    Call RE_environmental
End If

If Target can be multicell range use this one:

If Not Intersect(Target, Range("RE_1")) Is Nothing Then
    Call RE_environmental
End If

answered Apr 01 '14 at 17:22

Dmitry Pavliv

35,333
13
79
80

Is there a way to say "End If the cell becomes empty again"? – Anonymous N Apr 03 '14 at 18:10
not sure I understand you about `End If the cell becomes empty again`? what means again? when it becomes empty first time? clarify please – Dmitry Pavliv Apr 03 '14 at 22:32
If they decide they don't want to fill that specific cell anymore, is there a way to call off the macro? To tell it to take it back? – Anonymous N Apr 09 '14 at 04:28
It seems far from original question, please open new qusetion, and somebody will help you:) – Dmitry Pavliv Apr 09 '14 at 07:29

Mike Medina · Accepted Answer · 2014-04-06T22:44:46.393

1

Use your first answer but make this small change:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("RE_1").Address Then

        Call RE_environmental

    End If

End Sub

Simple syntax mistake!

Edit: To stop RE_environmental from running once the cell is empty, put the code from RE_environmental inside a do-while (not isempty(Range("RE_1"))) as long as RE_environmental is emptying "RE_1". The user won't be able to edit cells while RE_environmental is running.

edited Apr 06 '14 at 22:44

answered Apr 01 '14 at 17:44

Mike Medina

44
1
5

Is there a way to say "End If the cell becomes empty again"? – Anonymous N Apr 03 '14 at 18:14
Added answer to your question in my previous post. – Mike Medina Apr 06 '14 at 22:40

How to call a Macro when a named range (cell) is changed

2 Answers2