Excel VBA Worksheet Change Event Based on Multiple Cells

Looking for ways to use VBA Worksheet Change event based on multiple cells in Excel? Then, this is the right place for you.

To automate a task in Excel, you need to use the VBA Worksheet Change event. This post will give you clear instructions and useful examples to help you master this powerful feature, regardless of whether you need to track changes in continuous or non-continuous ranges. We’ll give you a clear, step-by-step tutorial in this blog post on how to use the VBA Worksheet Change Event Multiple Cells to monitor and react to changes in multiple cells.

How to Use VBA Worksheet Change Event Based on Multiple Cells in Excel: 2 Useful Examples

In the section that follows, we will examine two easy methods for managing the Worksheet_Change event based on multiple cells. Your VBA code will be more robust and flexible thanks to these methods, which give you the freedom to initiate particular actions when changes take place within a predetermined range.

1. VBA Worksheet Change Event Based on the Continuous Multiple Cells

In some circumstances, you might need to keep an eye on changes in a continuous range of cells rather than just a single cell. This blog post will walk you through an easy way to set up and modify the Worksheet_Change event handler for multiple cells in a continuous range, even if you have little to no experience with VBA.

Steps:

• In Excel, you can open the VBA Editor by pressing “Alt + F11” or by going to the “Developer” tab and selecting the “Visual Basic” button.
• Locate and choose the worksheet where you want to track changes in the VBA Editor.
• Enter the Worksheet Change Event code in the worksheet’s code module. Keep in mind that you must enter the event in the worksheet’s code module; you cannot modify it in a regular module. To do that, you can right-click on the sheet name and click on View Code.
• The worksheet’s code module should now contain the following VBA code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Specify the cells you want to monitor
    Dim monitoredRange As Range
    Set monitoredRange = Range("D5:D14") ' Modify this range as needed
    ' Check if the changed cells are within the monitored range
    If Not Intersect(Target, monitoredRange) Is Nothing Then
        ' Perform actions based on the changed cells
        ' Add your code here
        MsgBox "One of the monitored cell has been changed!"
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim monitoredRange As Range

    Set monitoredRange = Range("D5:D14") ' Modify this range as needed

    If Not Intersect(Target, monitoredRange) Is Nothing Then

        MsgBox "One of the monitored cells has been changed!"

    End If

End Sub

2. Worksheet Change Event Based on the Non-Continuous Multiple Cells

With simple steps for users with limited VBA knowledge to implement and modify as necessary, this blog post will walk you through an easy way to handle the Worksheet_Change event for multiple non-continuous cells.

Steps:

• By pressing “Alt + F11” or by going to the “Developer” tab and choosing the “Visual Basic” button in Excel, you can launch the VBA Editor.
• Find and select the worksheet in the VBA Editor where you want to keep track of changes.
• Enter the Worksheet Change Event code in the worksheet’s code module. You must enter the event in the worksheet’s code module; you cannot change it in a regular module, so keep that in mind.
• The following VBA code ought to now be present in the worksheet’s code module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Specify the cells you want to monitor
    Dim monitoredRange2 As Range
    Set monitoredRange2 = Range("D5:D7,D10:D14") ' Modify this range as needed
    ' Check if the changed cells are within the monitored range
    If Not Intersect(Target, monitoredRange2) Is Nothing Then
        ' Perform actions based on the changed cells
        ' Add your code here
        MsgBox "One of the monitored cells has changed!"
    End If
End Sub

• After changing the D7 cell, one of the cells in the range of D4:D7, Microsoft Excel will show a message

• However, the cell out of the ranges D4:D7 and D10:D14 has been changed, as in the following image. In response to that changes, Microsoft Excel.

How to Solve If VBA Worksheet Change Event is Not Working in Excel

There are times when an event might not go as planned. In this blog post, we will examine typical causes for the Worksheet Change event’s potential malfunction and offer workable solutions to VBA Worksheet Change event not working problem. A sample VBA code will also be provided, with the highlighted error serving to prevent the event from triggering.

Solution:

• You can start the VBA Editor by pressing “Alt + F11” or by going to the “Developer” tab and selecting the “Visual Basic” button in Excel.
• In the VBA Editor, choose the worksheet where you want to track changes.
• In the worksheet’s code module, type the Worksheet Change Event code. Keep in mind that you cannot change the event in a regular module. You must enter it in the worksheet’s code module.
• Now, the worksheet’s code module should contain the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Incorrect code: missing "Then" keyword
    If Target.Column = 1 ' Incorrect syntax
        Target.Offset(0, 1).Value = "Value Changed"
    End If
End Sub

• Here you need to correct any kind of error showing in the red line in the previous image. By adding “Then” you can solve the issue and fix the error.

Frequently Asked Questions (FAQs)

• How can I take various actions depending on the changed cells in the monitored range?

To perform particular actions based on the modified cells, you can add different code blocks or call different procedures inside the If statement block. Make each of the statement’s codes specific to the desired outcomes.

• Is it possible to use a single worksheet change event to track changes across multiple worksheets?

No, a specific worksheet is the only one that the Worksheet Change event refers to. You must add the Worksheet Change event code to each relevant worksheet’s code window to track changes across multiple worksheets.

• Why doesn’t my Worksheet Change event work properly?

There are a number of potential causes for the Worksheet Change event to fail, including an incorrect event name, disabled macros, incorrect macro security settings, and errors in the event code. Make sure you have named the event code correctly, turned on those macros, and the code is free from errors.

Download Practice Workbook

You can download the practice workbook from the following download button.

Conclusion

In this article, we looked at how to use the VBA Worksheet Change event to track changes in a number of cells in an Excel worksheet. Despite how straightforward it is, some common mistakes can prevent the event from running smoothly. We aimed to assist even those with limited VBA knowledge in successfully implementing and troubleshooting the Worksheet Change event by addressing these concerns and offering useful solutions.