Throughout your VBA practices, you would get the need to run macros when a certain range or cell changes. In that case, to run macros when a change is made to a target range, we use the change event. Events in VBA enables us to run the macros when a certain event occurs.
Syntax VBA Range Change Event
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("your_range")) Is Nothing Then
call your_macro
End If
End Sub
The Event does not work in modules. You need to write them into objects (worksheet, charts, workbook).
Lat's have an example to learn how to run a macro when a change is made in a specified range.
First, we choose the sheet on which the event will occur. Double click on that sheet in VBA editor and copy below code or generic code above and make changes as per your requirement.
In this example, I want to run a macro/VBA code when a change is made in range A2: A100 on sheet 2. To do so, I double click on sheet2 in project explorer. It opens the coding page for that sheet. You can right-click on the sheet and click on the view code to do the same.
I need to use the change event. For that, we use default subroutine Worksheet_Change(ByVal Target As Range). It triggers when a specified change is made. So our code is this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
Call TestEvent
End If
End Sub
When you make any changes in range A2:A100 on Sheet2, the subroutine TestEvent will be called, as you can see in the gif above.
The TestEvent is the public subroutine in module2. It simply pops up a message that the Event is working.
Sub TestEvent() MsgBox "Event is working!" End Sub
How does it work?
We have put a condition :
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
Here Intersect(Target, Range("A2:A100")) Is Nothing returns True if no change is made in Range A2:A100. We put a Not operator before this statement that reverses the output given by "Intersect(Target, Range("A2:A100"))". Hence, if no change is made in range A2:A100, the expression returns Falls and the sub TestEvent doesn't get the call. If you do make a change in any cell in range A2:A100, the expression will return True and the event will occur. And that's what happening here.
Note: You can put any subroutine in this block. It can be from any module. But it should be a public subroutine. You can set the range to any extent. The whole sheet is the limit.
So yeah guys, this how you can call a subroutine or run a macro/VBA code when a change is made in a specified range. It was a basic event example. This event will trigger only when the change is text-based. By text-based I mean if you write anything in the cells or delete, the event will trigger. If you change the formatting of the cells, it will not trigger. There are other methods to do that.
I hope it was helpful. If you have any queries related to this VBA topic or any other excel related topic, let me know the comments section below. And check out our other related and popular posts mentioned below.
Related Articles:
Run Macro When any Change is Made On Sheet | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.
Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.
The Worksheet Events in Excel VBA | The worksheet event are really useful when you want your macros run when a specified event occurs on the sheet.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
