CODEWITHSUNDEEP
excelvba
kamelkid2
kamelkid2

Reputation: 534

excel VBA run macro automatically whenever a cell is changed

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.

Upvotes: 26

Views: 197021

Answers (5)

Peter Albert
Peter Albert

Reputation: 17505

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

Upvotes: 45

David Blackburn
David Blackburn

Reputation: 25

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

Upvotes: 1

Bendaua
Bendaua

Reputation: 341

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

Upvotes: 2

Zediiiii
Zediiiii

Reputation: 790

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

Upvotes: 2

user2140261
user2140261

Reputation: 8003

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.

Upvotes: 21

Related Questions