Excel worksheet_change事件 [英] Excel worksheet_change event

问题描述

我想知道您是否可以为两个不同的目标提供关于worksheet_Change的帮助.

I am wondering if you could help me with the worksheet_Change for two different targets.

我的代码如下:

Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("A1:A50")) Is Nothing Then Exit Sub        
             Range("A1:A50").Copy ThisWorkbook.Sheets(2).Range("B1")

 End Sub

这是可行的，只要在A列中更新Anytjing，它就会被更新并复制到B列中(工作表2).现在我遇到的问题是，我想添加另一个目标B1:B50，并且只有在该目标中包含单词SUM的情况下，它才应该复制和更新.问题在于它正在复制但未更新任何内容.我的代码是:

And it is working, whenever anytjing is updated in column A it is updated and copied in column B(worksheet 2). Now the problem I have is that i want to add another target, B1:B50, and only If that target has the word SUM in it, it should copy and update. The problem is that it is copying but NOT updating anything. My code for that is:

Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("A1:A50, B1:B50")) Is Nothing Then Exit Sub    
    If Target.Address = "TOTAL" Then
             Range("A1:A50").Copy ThisWorkbook.Sheets(2).Range("B1")
       end if
 End Sub

我应该做什么?

推荐答案

我认为您对Target表示的内容感到困惑. Target，在Worksheet_Change事件范围内，是已更改的单元格.

I think you're confused on what Target represents. Target, in the scope of a Worksheet_Change event is the cell(s) being changed.

此外，Target.Address将从不变为"TOTAL"，因此此逻辑将不起作用:

Also, the Target.Address will never be "TOTAL", so this logic will not work:

If Target.Address = "TOTAL"

考虑到这一点，我会做这样的事情.为每个范围(列A或B)创建一个单独的过程.然后使用Worksheet_Change事件来确定要调用的过程:

With that in mind, I would do something like this. Create a separate procedure for each range (column A or B). Then use the Worksheet_Change event to determine which procedure to call on:

Private Sub Worksheet_Change(ByVal Target As Range)

 If Intersect(Target, Range("A1:A50, B1:B50")) Is Nothing Then Exit Sub    
 If Target.Column = 1 Then Call RangeA(Target)
 If Target.Column = 2 Then Call RangeB(Target)
End Sub

Sub RangeA(Target as Range)
    Range("A1:A50").Copy ThisWorkbook.Sheets(2).Range("B1")
End Sub

Sub RangeB(Target As Range)

    'Here, you put the code that you want to run when Target is in column B

End Sub

由于我不确定您打算对列B做什么，因此我暂时将RangeB过程留空.您可以根据范围B1:B50中的目标更改来修改该过程以执行所需的操作.

Since I'm not exactly sure what you intend to do with Column B, I left the RangeB procedure empty for now. You can modify that procedure to do whatever you want, based on the target change in range B1:B50.

这篇关于Excel worksheet_change事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！