Worksheet_Change-同时定位多个单元格 [英] Worksheet_Change - Targeting multiple cells simultaneously

问题描述

VBA初学者在这里.

VBA beginner here.

我有一个为用户指定输入单元格的项目.当这些输入单元之一改变时，它需要运行几行仅特定于该单元的代码.如果用户清除了单元格的内容，我希望代码将空白单元格替换为值"0".

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

在单个单元格上执行更改时，上述所有操作均正常.如果用户选择 all 所有单元格并按Delete键，则仅运行第一个单元格的代码.我希望它为所有选定(删除)的单元格运行.

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.

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

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

谢谢

推荐答案

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

这篇关于Worksheet_Change-同时定位多个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！