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

User · Accepted Answer

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       End If Finalize              Application EnableEvents   True End Sub

User · Answer

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    amp  Target Row     MsgBox  You changed it to     amp  Target Value Finalize      Application EnableEvents   True End Sub

User · Answer

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

User · Answer

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

User · Answer

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  gt  0 Then  mycode here end if end sub

[excel] excel VBA run macro automatically whenever a cell is changed

Examples related to excel

Examples related to vba