excel VBA run macro automatically whenever a cell is changed

Question

Is there a simple way to get Excel to automatically execute a macro whenever a cell is changed?

The cell in question would be in Worksheet("BigBoard").Range("D2")

What I thought would be a simple Google inquiry is proving to be more complicated - every sample involved intersects (whatever those are) or color formatting or any other number of things that appear to be irrelevant.

Use the answer by Peter Albert. Don't be scared of the line with intersect, that's just so VBA knows which cells you are monitoring for changes. And where he has MsgBox, that's where you call your macro. This is the simplest way to do it. — Dan, Mar 11 '13 at 11:12
possible duplicate of [automatically execute an Excel macro on a cell change](http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) — MackM, Aug 12 '15 at 18:31
Could you please mark the answer that worked for you, so this question is no longer shown as unanswered? Thank you in advance! — Marcus Mangelsdorf, Nov 09 '15 at 10:11

score 44 · Accepted Answer · edited Dec 24 '22 at 03:50

44

Yes, this is possible by using worksheet events:

In the Visual Basic Editor open the worksheet you're interested in (i.e. "BigBoard") by double clicking on the name of the worksheet in the tree at the top left. Place the following code in the module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("D2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False 'to prevent endless loop
    On Error Goto Finalize 'to re-enable the events      
    MsgBox "You changed THE CELL!"
Finalize:        
    Application.EnableEvents = True
End Sub

edited Dec 24 '22 at 03:50

chris neilsen

52,446
10
84
123

answered Mar 11 '13 at 11:10

Peter Albert

16,917
5
64
88

this works very well thank you very much. in the interest of fully understanding, how does the expression intersect(target)...perform in more plain speaking? – kamelkid2 Mar 11 '13 at 11:18
2

It tests that D2 was in the range of cells / or the cell changed (ie - part of Target). If not the code exits. +1 btw – brettdj Mar 11 '13 at 11:30
3

`Insersect(rng1,rng2)` returns the range where both input range "intersect". therefore, if the intersection of the changed cells and D2 is nothing, something else was changed and you don't want the macro to procede. BTW: If it solved your problem, please mark it as the solution by clicking on the checkmark! – Peter Albert Mar 11 '13 at 11:57
1

There is a similar solution in the official Microsoft documentation at http://support.microsoft.com/kb/213612 – Rintze Zelle Jul 19 '13 at 13:01
3

+1 for telling me to use this code in the worksheet, I had it in a module and it wasnt working, as soon as I put it in the worksheet, it worked :) – hammythepig Apr 27 '16 at 21:12
1

this works really well. but I'm making some modifications to it, and I notice if I write jenky code, and it errors out, I'm never able to run this macro again, even if I rewrite the macro to it's original state. I think it may have something to do with the Application.EnableEvents option. Can anyone speak to this? – Mark Romano Oct 24 '16 at 17:11
2

@MarkRomano you're right - in case of an error the enable events was not reset. I extended the code. – Peter Albert Oct 24 '16 at 17:25
**Bug**: if your cell's data-validated, and you type a non-valid value, after Excel displays "This value doesn't match the data validation restrictions defined for this cell" and you clicked on _Cancel_, the custom message "You changed THE CELL!" will still appear not only once but twice, even though you didn't change the value. – alejnavab Feb 26 '17 at 16:57

score 21 · Answer 2 · edited Jul 22 '15 at 13:54

21

Another option is

Private Sub Worksheet_Change(ByVal Target As Range)
    IF Target.Address = "$D$2" Then
        MsgBox("Cell D2 Has Changed.")
    End If
End Sub

I believe this uses fewer resources than Intersect, which will be helpful if your worksheet changes a lot.

edited Jul 22 '15 at 13:54

TarkaDaal

18,798
7
34
51

answered Mar 11 '13 at 13:12

user2140261

7,855
7
32
45

1

Simpler and without the bug I mentioned in the selected answer. – alejnavab Feb 26 '17 at 17:18

score 2 · Answer 3 · answered Jul 24 '15 at 17:15

In an attempt to find a way to make the target cell for the intersect method a name table array, I stumbled across a simple way to run something when ANY cell or set of cells on a particular sheet changes. This code is placed in the worksheet module as well:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 0 Then
'mycode here
end if
end sub

score 1 · Answer 4 · answered Apr 10 '18 at 09:01

In an attempt to spot a change somewhere in a particular column (here in "W", i.e. "23"), I modified Peter Alberts' answer to:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Column = 23 Then Exit Sub
    Application.EnableEvents = False             'to prevent endless loop
    On Error GoTo Finalize                       'to re-enable the events
    MsgBox "You changed a cell in column W, row " & Target.Row
    MsgBox "You changed it to: " & Target.Value
Finalize:
    Application.EnableEvents = True
End Sub

score 1 · Answer 5 · edited Mar 28 '19 at 14:37

I was creating a form in which the user enters an email address used by another macro to email a specific cell group to the address entered. I patched together this simple code from several sites and my limited knowledge of VBA. This simply watches for one cell (In my case K22) to be updated and then kills any hyperlink in that cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("K22")

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

        Range("K22").Select
        Selection.Hyperlinks.Delete

    End If 
End Sub

excel VBA run macro automatically whenever a cell is changed

5 Answers5

Linked

Related