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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] automatically execute an Excel macro on a cell change

Examples related to excel

Examples related to vba

Examples related to automation