Excel VBA 事件处理程序

Excel VBA Event Handlers

提问by Vivek

1) Can someone please tell me what the issue could be with this code?

1）有人可以告诉我这段代码有什么问题吗？

2) I need this code actually to run on a worksheet update, but @ first-time load of the workbook, i'm running an update using the Workbook_Openevent handler. Won't this trigger my Worksheet_Changeevent as well? Is there any way to avoid this

2）我实际上需要此代码在工作表更新上运行，但是@第一次加载工作簿，我正在使用Workbook_Open事件处理程序运行更新。这不会也触发我的Worksheet_Change事件吗？有什么办法可以避免这种情况

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim rowCount As Integer

Set Worksheet = "datasheet"
Set rowCount = ws.Cells(Rows.Count, "A").End(xlUp)

If Not Intersect(Target, Range("M3:M" & CStr(rowCount))) Is Nothing Then
   MsgBox ("Hi")
End If
Else
If Not Intersect(Target, Range("T3:T" & CStr(rowCount))) Is Nothing Then
   MsgBox ("Hi")
End If
Else
If Not Intersect(Target, Range("X3:X" & CStr(rowCount))) Is Nothing Then
   MsgBox ("Hi")
End If
Else
If Not Intersect(Target, Range("AB3:AB" & CStr(rowCount))) Is Nothing Then
   MsgBox ("Hi")
End If
Else
If Not Intersect(Target, Range("AI3:AI" & CStr(rowCount))) Is Nothing Then
   MsgBox ("Hi")
End If

End Sub

I'm getting a Compiler error saying "object required" when i change my data with this handler. On the other hand, if i give values instead of taking a rowcount, I get no issues.

当我使用此处理程序更改数据时，出现编译器错误，提示“需要对象”。另一方面，如果我给出值而不是行计数，我不会遇到任何问题。

回答by brettdj

You have several issues

你有几个问题

1. As per Sid's comment you should use rowCount = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
2. You should be using Set ws = Sheets("datasheet")not Set Worksheet = "datasheet"
3. Your Elsestatements are causing errors as they are orphaned. If you want to exit on the first "Hi" rather than continuing to test you should try something like this

1. 根据 Sid 的评论，您应该使用 rowCount = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
2. 你应该使用Set ws = Sheets("datasheet")notSet Worksheet = "datasheet"
3. 您的Else语句是孤立的，因此会导致错误。如果你想在第一个“嗨”退出而不是继续测试你应该尝试这样的事情

Sid has covered your other point. You can use a Booleanvariable for this

Sid 已经涵盖了你的另一点。您可以Boolean为此使用一个变量

updated code

更新代码

this tests the intersect on columns M, T, X, AB and AI from row 3 to row rowCountin a single line

此测试从3行的列M，T，X，AB和AI的相交来排rowCount在一行

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim rowCount As Long

Set ws = Sheets("datasheet")
rowCount = ws.Cells(Rows.Count, "A").End(xlUp).Row

If Not Intersect(Target, Range("M3:M" & CStr(rowCount) & ",T3:T" & CStr(rowCount) & ",X3:X" & CStr(rowCount) & ",AB3:AB" & CStr(rowCount) & ",AI3:AI" & CStr(rowCount))) Is Nothing Then MsgBox ("Hi")

End Sub

回答by Siddharth Rout

I always recommend this when using Worksheet_Change

我在使用时总是推荐这个 Worksheet_Change

1. You do not need the sheet name. It is understood that the code is to be run on current sheet unless you are trying to use another sheet row as a reference as correctly mentioned by brettdj in the comments below.

2. Whenever you are working with Worksheet_Changeevent. Always switch Off events if you are writing data to the cell. This is required so that the code doesn't go into a possible endless loop

3. Whenever you are switching off events, use error handling else if you get an error, the code will not run the next time.

1. 您不需要工作表名称。据了解，代码将在当前工作表上运行，除非您尝试使用另一个工作表行作为参考，正如 brettdj 在下面的评论中正确提到的那样。

2. 每当您处理Worksheet_Change事件时。如果您正在向单元格写入数据，请始终关闭事件。这是必需的，以便代码不会进入可能的无限循环

3. 每当您关闭事件时，请使用错误处理，否则如果出现错误，代码下次将不会运行。

Here is an example

这是一个例子

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    '
    '~~> Rest of the code
    '

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

So using the above, your code becomes (UNTESTED)

因此，使用上面的代码，您的代码变为 (UNTESTED)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowCount As Long

    On Error GoTo Whoa

    Application.EnableEvents = False

    With ActiveSheet
        rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With


    If Not Intersect(Target, Range("M3:M" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("T3:T" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("X3:X" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("AB3:AB" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("AI3:AI" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

EDIT:

编辑：

Regarding your 2nd question. As I mentioned in the comment above, you can use a Public variable to check if the worksheet change event is being caused by the workbook open.

关于你的第二个问题。正如我在上面的评论中提到的，您可以使用公共变量来检查工作表更改事件是否是由工作簿打开引起的。

Place this code in a module.

将此代码放在一个模块中。

Public WasWbOpened As Boolean

Place this code in the workbook code area

将此代码放在工作簿代码区域中

Option Explicit

Private Sub Workbook_Open()
    WasWbOpened = True
    '
    '~~> Rest of the code
    '
    WasWbOpened = False
End Sub

And change your worksheet change event to

并将您的工作表更改事件更改为

Private Sub Worksheet_Change(ByVal Target As Range)
    If WasWbOpened = True Then Exit Sub

    Dim rowCount As Long

    On Error GoTo Whoa

    Application.EnableEvents = False

    With ActiveSheet
        rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With


    If Not Intersect(Target, Range("M3:M" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("T3:T" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("X3:X" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("AB3:AB" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    ElseIf Not Intersect(Target, Range("AI3:AI" & rowCount)) Is Nothing Then
       MsgBox ("Hi")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub