By namin


2009-01-03 17:33:22 8 Comments

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?

5 comments

@Joe 2009-01-03 17:36:36

Handle the Worksheet_Change event or the Workbook_SheetChange event.

The event handlers take an argument "Target As Range", so you can check if the range that's changing includes the cell you're interested in.

@namin 2009-01-03 21:33:52

Thanks, it works. I check the range with, say, Target.Address = Range("H5").Address. Is there an easier way?

@namin 2009-01-03 21:38:59

An alternative: Not (Intersect(Target, Range("H5")) Is Nothing) . Is this how you would do it?

@Ant 2009-04-27 10:45:01

The first comment (Target.Address = Range("H5").Address) wouldn't work if your cell was only part of the changed range. The second comment still suffers the problems described by Mike Rosenblum.

@Mike Rosenblum 2009-01-06 01:59:23

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...

@dhpratik 2013-09-30 09:58:30

what if cell H5 is changed from another sheet, say sheet2 then the above function doesn't work. plz help in this.

@hammythepig 2016-04-27 21:14:18

For anyone coming here from a google search, make sure you paste this code into the sheet in vba, not a module like I did. look at stackoverflow.com/questions/15337008/…

@Pierre 2016-09-30 15:44:06

Application.ActiveSheet.Range("H5"). ==> target.parent.range("H5") is even safer

@Scott Marcus 2017-05-12 14:01:36

@WillEdiger Whenever you don't explicitly specify a sheet reference, Excel assumes the ActiveSheet and whenever you don't explicitly specify that it is Excel you are working with, Excel assumes Application.

@YowE3K 2018-01-10 06:59:20

Note that, in a worksheet code module (which is where a Worksheet_Change event must be located), an unqualified Range does not default to ActiveSheet but instead refers to the sheet containing the code. The code in this answer is therefore effectively the same as the code in the question. (Note: Back in 2009 when this answer was written, it may have been different, but I'm pretty certain it wasn't.)

@Mike Rosenblum 2018-01-17 17:30:06

@YowE3K: Nice pickup, you are correct.

@Eric Vaughn-Shobey 2018-07-11 22:51:50

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.

@Juan Garcia 2010-04-20 16:46:12

I have a cell which is linked to online stock database and updated frequently. I want to trigger a macro whenever the cell value is updated.

I believe this is similar to cell value change by a program or any external data update but above examples somehow do not work for me. I think the problem is because excel internal events are not triggered, but thats my guess.

I did the following,

Private Sub Worksheet_Change(ByVal Target As Range) 
  If Not Intersect(Target, Target.Worksheets("Symbols").Range("$C$3")) Is Nothing Then
   'Run Macro
End Sub

@David Van der Vieren 2013-03-28 20:51:53

I can not get this to work for some reason. When I tell the code to run in VBA it is pulling up a pop up menue and asking me if I want to run the macro instead of running the macro automaticaly?

@Javier Torón 2009-01-04 11:58:04

I prefer this way, not using a cell but a range

    Dim cell_to_test As Range, cells_changed As Range

    Set cells_changed = Target(1, 1)
    Set cell_to_test = Range( RANGE_OF_CELLS_TO_DETECT )

    If Not Intersect(cells_changed, cell_to_test) Is Nothing Then 
       Macro
    End If

@Shai Alon 2015-12-20 09:58:39

It's the same as one cell. You can either set a range as one cell, range of continuos cells, or even scattered cells (all separated by comma).

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1046128 View
  • 1801 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

5 Answered Questions

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

  • 2013-03-11 11:05:50
  • kamelkid2
  • 167595 View
  • 22 Score
  • 5 Answer
  • Tags:   excel vba

1 Answered Questions

Automatic execution of Macro based on cell change

  • 2019-03-04 14:15:18
  • agermanguy
  • 32 View
  • 0 Score
  • 1 Answer
  • Tags:   vba

1 Answered Questions

[SOLVED] Using Autofill in Excel with Protected cells

  • 2018-03-05 22:27:13
  • Alonzo Preciado
  • 284 View
  • 0 Score
  • 1 Answer
  • Tags:   excel-vba vba excel

1 Answered Questions

excel on change not working if cell value is changed by another module

  • 2017-10-23 15:58:20
  • Avi Sangray
  • 499 View
  • 1 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

2 Answered Questions

[SOLVED] Excel VBA Change From email address with IBM Notes?

1 Answered Questions

1 Answered Questions

[SOLVED] Get Worksheet_change event to execute macro automatically

  • 2013-03-13 04:16:09
  • user2161965
  • 4202 View
  • 2 Score
  • 1 Answer
  • Tags:   excel excel-vba vba

1 Answered Questions

[SOLVED] Create an automatic date stamp in excel from an entry

  • 2010-05-10 02:33:14
  • Obfus
  • 2481 View
  • 1 Score
  • 1 Answer
  • Tags:   excel vba

Sponsored Content