vba Worksheet_Change - 同时定位多个单元格

Worksheet_Change - Targeting multiple cells simultaneously

提问by Snotty

VBA beginner here.

VBA初学者在这里。

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".

我有一个项目，我为用户指定了输入单元格。当这些输入单元格之一发生更改时，它需要运行几行代码，这些代码仅针对该单元格。如果用户清除单元格的内容，我希望代码用值“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 allthe 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?

关于如何在多个单元格中同时运行 Worksheet_Change 的任何建议？

Thanks

谢谢

采纳答案by Tim Williams

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Cells(1, 1)) Is Nothing 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 Not Application.Intersect(Target, Cells(1, 2)) Is Nothing 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 Not Application.Intersect(Target, Cells(1, 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

回答by teylyn

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

当您有一个在单个单元格上运行良好的更改事件时，您可以进行一些调整以确保它在您一次性更改一系列单元格时也能正常工作，例如将三个单元格粘贴到 A1 到 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

If Not Intersect(Target, Range("A1:A3")) Is Nothing Then-- 仅对单元格 A1 到 A3 的更改起作用

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

For Each cel In Target- 对所有已更改的单元格执行相同操作

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

Range("B" & myRow).Value = "Changed " & Target.Row- 在当前行的 B 列中输入一些值

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 ....

在宏的下一步中，我们可能会将一些数据输入到我们正在监视更改的单元格中，即 A1 到 A3。这些单元格的更改将触发此宏。此宏将写入单元格。单元格中的更改将触发此宏....

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.

Application.EnableEvents = False- 现在任何诸如“单元格已更改”之类的事件都将被忽略。

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.

我们现在可以将一个值写入 A 列，并且不会再次触发宏。伟大的。我们对单元格 A1 到 A3 做任何我们需要做的事情，然后我们重新打开事件监控。

Application.EnableEvents = True

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.

让我知道这是否有帮助，或者您是否需要更多细节。这些事情需要一点学习曲线。