{"id":387,"date":"2019-01-22T21:04:35","date_gmt":"2019-01-22T21:04:35","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=387"},"modified":"2019-02-09T03:57:48","modified_gmt":"2019-02-09T03:57:48","slug":"how-to-run-a-macro-when-cell-value-changes-in-excel-vba","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/01\/22\/how-to-run-a-macro-when-cell-value-changes-in-excel-vba\/","title":{"rendered":"How To Run A Macro When Cell Value Changes In Excel VBA"},"content":{"rendered":"

Sometimes you want to be able to monitor a range of cells in your worksheet for any changes. Using this, you can run a macro when a cell changes to a specific value<\/strong>.<\/p>\n

Here I will show how to run a macro when a cell value changes in Excel VBA<\/strong><\/p>\n

This post will also answer the question you may have, “How do I automatically trigger a macro in Excel?” When the cell value changes, we will trigger a macro to run.<\/p>\n

In the following example, I am monitoring the range “A1:A4” (blue shading) for changes. <\/p>\n

I want the user to be made aware when the value of the cell changes and what the previous value of the cell was<\/strong>. That way I can write code to have excel vba undo the cell change<\/strong> .<\/p>\n

\"\"<\/a><\/p>\n

Here’s the code<\/h2>\n
Dim m_dblOldValue As Double\r\n\r\n\r\nPrivate Sub Worksheet_Change(ByVal Target As Range)\r\n    \r\n    Dim rngMonitor As Range\r\n    Dim rngCell As Range\r\n    \r\n    'monitor this range of cells\r\n    Set rngMonitor = Range(\"A1:A4\")\r\n    \r\n    '*********************************************\r\n    'was one of the values in the range changed?\r\n    '*********************************************\r\n    \r\n    'loop through each cell in the range...\r\n    For Each rngCell In rngMonitor\r\n        'if the current cell address matches one of the cells to monitor, flag it with a message\r\n        \r\n        If Target.Address = rngCell.Address Then\r\n           'I get the previous value of the cell from the \"SelectionChange\" event\r\n            MsgBox Target.Address & \" changed from \" & m_dblOldValue & \" to \" & Target.Value\r\n        End If\r\n    Next\r\n    \r\nEnd Sub\r\n\r\nPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)\r\n    'Get the previous value of the changed cell\r\n    m_dblOldValue = Target.Value\r\nEnd Sub\r\n<\/pre>\n
In the “Worksheet_SelectionChange” event, I can get the previous value.
\nIn the “Worksheet_WorksheetChange” event, I can monitor the cell changes.<\/p>\n
Watch me do it:<\/p>\n