Run macro automatically when cell value changed (not changed by user)

Question

Is there a way to have a macro changed when the value is changed is changed, not necessarily when a user changes the value?

For example, I have a check box that is linked to a cell "A1". When the check box is checked, A1 reads "TRUE" when it is not checked it reads "FALSE". When A1 changes between TRUE and FALSE I want a macro to run.

I have seen the Private Sub Worksheet_Change code, but that seems to only work when a user actually changes cell A1. Is there another way to automatically run the macro?

UPDATE 11/29/13 Thank you everyone for your suggestions, but unfortunately it isn't quite working out. Let me try to expand further.

I have a user form with check boxes, drop down lists, and text fields. All of the data from the user form is summarized in column B of a worksheet (which is where all the user form fields are linked). When the macro runs, some calculations happen and the user get's some numbers.

When the user changes something in the user form I want the macro to run automatically. I know there is a possibility that this can become resource intensive, but I'll deal with that problem when it comes to it. I would like to have a procedure that says if the value of any cell in range B1:B20 changes then run the macro. I believe this method is easier than telling every user form field to run the same macro because it will be easier to expand and update this form later, especially if someone else takes over maintenance.

I could just have a button that the user can click to run the macro when they are done filling in the form, but I can foresee inaccurate information because the user forgets to recalculate; this is why I would like to have it done automatically and numbers are updated in real time.

possible duplicate of [VBA code doesn't run when cell is changed by a formula](http://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula) — Siddharth Rout, Nov 28 '13 at 23:19
@Sid the suggested duplicate Q deals with changes due to Formulas and your answer suggests `Worksheet_Calculate`. Unfortunately, a cell change due to a linked check box doesn't trigger this event either. — chris neilsen, Nov 29 '13 at 05:08
I don't understand your question. A value is always changed by a user? In your case, the check box is changed by a user, so you must track it. — jacouh, Nov 29 '13 at 08:33
when I have the worksheet_change event enabled, it fires if the change was made via code. Im not sure why this isn't working for you — John Smith, May 31 '15 at 14:40
Does the RunMacroWhenValueChanges UDF in FormulaDesk work as you expect? http://www.formuladesk.com — Gareth Hayter, Jun 03 '15 at 04:07
Why don't you use 'checkbox_click' event, if the user makes his changes by a click? — ali srn, May 04 '16 at 05:35

score -1 · Answer 1 · edited Nov 28 '13 at 23:12

-1

From what I can tell you must enumerate the cells that could be changed or act upon any cell change. Here is a sample function.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" or Target.Address = "$A$2" Then
        Sheet1.Range("A3").Interior.Color = RGB(255, 0, 0)
    End If
End Sub

edited Nov 28 '13 at 23:12

psxls

6,807
6
30
50

answered Nov 28 '13 at 22:51

Delbert Aud

29
3

`Worksheet_Change` is triggered when a cell is changed _by the user_. The OP specifically states he want to detect a change for _any_ reason, so this answer won't work for him. – chris neilsen Nov 29 '13 at 05:05

Patrick Lepelletier · Answer 2 · 2013-11-29T22:01:07.780

Worksheet_Change works ALWAYS, but you need to have application.enableevents=true or it wont. But i don't like the if target.address part, i prefer :

Private Sub Worksheet_Change(ByVal Target As Range)
select case target.address 'or target.row, column...
  case "$A$1"
    application.enableevents=false 'or the change sub will trigger again on changes!
    code
    application.enableevents=true
  case "$A$2"
    application.enableevents=false 'or the change sub will trigger again on changes!
    code
    application.enableevents=true
end select
end sub

score -1 · Answer 3 · answered Jan 31 '14 at 18:46

-1

Use "Application.Volatile" in the first line of your VBA function

Function A()
    Application.Volatile


End Function

answered Jan 31 '14 at 18:46

Roger Barreto

2,004
1
17
21

Run macro automatically when cell value changed (not changed by user)

3 Answers3