automatically execute an Excel macro on a cell change

Question

How can I automatically execute an Excel macro each time a value in a particular cell changes?

Right now, my working code is:

Private Sub Worksheet_Change(ByVal Target As Range)     If Not Intersect(Target, Range("H5")) Is Nothing Then Macro End Sub 

where "H5" is the particular cell being monitored and Macro is the name of the macro.

Is there a better way?

Answer 1

Your code looks pretty good.

Be careful, however, for your call to Range("H5") is a shortcut command to Application.Range("H5"), which is equivalent to Application.ActiveSheet.Range("H5"). This could be fine, if the only changes are user-changes -- which is the most typical -- but it is possible for the worksheet's cell values to change when it is not the active sheet via programmatic changes, e.g. VBA.

With this in mind, I would utilize Target.Worksheet.Range("H5"):

Private Sub Worksheet_Change(ByVal Target As Range)     If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then Macro End Sub 

Or you can use Me.Range("H5"), if the event handler is on the code page for the worksheet in question (it usually is):

Private Sub Worksheet_Change(ByVal Target As Range)     If Not Intersect(Target, Me.Range("H5")) Is Nothing Then Macro End Sub 

Hope this helps...

Answer 2

I spent a lot of time researching this and learning how it all works, after really messing up the event triggers. Since there was so much scattered info I decided to share what I have found to work all in one place, step by step as follows:

1) Open VBA Editor, under VBA Project (YourWorkBookName.xlsm) open Microsoft Excel Object and select the Sheet to which the change event will pertain.

2) The default code view is "General." From the drop-down list at the top middle, select "Worksheet."

3) Private Sub Worksheet_SelectionChange is already there as it should be, leave it alone. Copy/Paste Mike Rosenblum's code from above and change the .Range reference to the cell for which you are watching for a change (B3, in my case). Do not place your Macro yet, however (I removed the word "Macro" after "Then"):

Private Sub Worksheet_Change(ByVal Target As Range)     If Not Intersect(Target, Me.Range("H5")) Is Nothing Then End Sub 

or from the drop-down list at the top left, select "Change" and in the space between Private Sub and End Sub, paste If Not Intersect(Target, Me.Range("H5")) Is Nothing Then

4) On the line after "Then" turn off events so that when you call your macro, it does not trigger events and try to run this Worksheet_Change again in a never ending cycle that crashes Excel and/or otherwise messes everything up:

Application.EnableEvents = False 

5) Call your macro

Call YourMacroName 

6) Turn events back on so the next change (and any/all other events) trigger:

Application.EnableEvents = True 

7) End the If block and the Sub:

    End If End Sub 

The entire code:

Private Sub Worksheet_Change(ByVal Target As Range)     If Not Intersect(Target, Me.Range("B3")) Is Nothing Then         Application.EnableEvents = False         Call UpdateAndViewOnly         Application.EnableEvents = True     End If End Sub 

This takes turning events on/off out of the Modules which creates problems and simply lets the change trigger, turns off events, runs your macro and turns events back on.