excel - Worksheet_Change - Targeting multiple cells simultaneously

Question

VBA beginner here.

I have project where I have specified input cells for the user. When one of these input cells is changed, it needs to run a few lines of code that are specific to only that one cell. If the user clears the contents of the cell, I want the code to replace the blank cell with the value "0".

The code below simulates what I am trying to achieve. It is written in the same form as my project but is more succinct.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Row = 1 Then
    Range("B1").Value = "Changed 1"  'Just something specific to this cell. Not important
    If IsEmpty(Sheet1.Range("A1")) Then Sheet1.Range("A1").Value = 0
End If

If Target.Column = 1 And Target.Row = 2 Then
    Range("B2").Value = "Changed 2" 'Just something specific to this cell. Not important
    If IsEmpty(Sheet1.Range("A2")) Then Sheet1.Range("A2").Value = 0
End If

If Target.Column = 1 And Target.Row = 3 Then
    Range("B3").Value = "Changed 3" 'Just something specific to this cell. Not important
    If IsEmpty(Sheet1.Range("A3")) Then Sheet1.Range("A3").Value = 0
End If

End Sub

Everything above works fine when the changes are performed on single cells. If the user selects all the cells and presses the delete key, it only runs the code for the first cell. I want it to run for all the selected (deleted) cells.

Any advice on how to simultaneously run the Worksheet_Change across multiple cells?

Thanks

Answer 1

When you have a change event that works just fine on a single cell, you can make a few adjustments to ensure that it also works when you change a range of cells in one go, like when you paste three cells into A1 to A3

You may want to apply an approach similar to this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim cel As Range
Dim myRow As Long

Set ws = ThisWorkbook.Sheets("Sheet1")

If Not Intersect(Target, Range("A1:A3")) Is Nothing Then ' watch all the cells in this range
    For Each cel In Target ' do the next steps for each cell that was changed
        myRow = cel.Row
        Range("B" & myRow).Value = "Changed " & Target.Row 'Just something specific to this cell. Not important
        Application.EnableEvents = False
        If IsEmpty(ws.Range("A" & myRow)) Then Sheet1.Range("A" & myRow).Value = 0
        Application.EnableEvents = True
    Next cel
End If

End Sub

Explanation:

If Not Intersect(Target, Range("A1:A3")) Is Nothing Then -- only act on changes to cells A1 to A3

For Each cel In Target - do the same thing for all cells that have been changed

Range("B" & myRow).Value = "Changed " & Target.Row - enter some value into column B of the current row

In the next step of the macro we will possibly enter some data into the cells we are monitoring for a change, i.e. A1 to A3. A change in these cells will trigger this macro. This macro will write into the cells. A change in the cells will trigger this macro ....

You see where this is going. In order to avoid an endless loop, we turn off any event triggered macros, e.g. macros that fire when a cell is changed. So we turn off event monitoring with this statement.

Application.EnableEvents = False - Now any events like "a cell has been changed" will be ignored.

We can now write a value into column A and that will not trigger the macro again. Great. We do whatever we need to do to cells A1 to A3 and then we turn event monitoring back on.

Application.EnableEvents = True

Then we go to the next cell (if any) in the range that triggered this macro.

Let me know if that helps or if you need a bit more detail. These things take a little learning curve.