VBA报错：方法'Range'作用于对象'_Worksheet'失败 求助

场景说明 #

设置了工作表变更事件，期望用户从数据验证单元格选择选项后自动更新内容、修改"Lists"表单元格值，但事件触发时报错，手动按F5运行代码完全正常。

相关代码 #

核心执行过程（test 子程序）

Sub test()
    Dim j As Integer
    j = 2
    If ActiveSheet.Name = "Email" Then
        Sheets("Lists").Range("Z2:Z31").Cells.Value = ""
        For i = 2 To 190
            If Sheets("Lists").Cells(i, 8).Value = Cells(2, 2).Value Then
                Sheets("Lists").Cells(j, 26).Value = Sheets("Lists").Cells(i, 6).Value
                j = j + 1
            End If
        Next
        Range("C12:O12").AutoFilter 1, ">=" & Range("B1").Value, xlAnd, "<=" & Range("C1").Value
    End If
End Sub

类模块事件绑定代码（Class1）

Public WithEvents appevent As Application
Private Sub appevent_SheetChange(ByVal sh As Object, ByVal target As Range)
    Call test
End Sub

工作簿初始化代码（ThisWorkbook）

Dim myobject As New Class1
Private Sub Workbook_Open()
    Set myobject.appevent = Application
End Sub

问题根源 #

你的猜测正确，问题出在未明确指定工作表对象：

• 手动运行时，你通常处于"Email"工作表，Cells(2,2)、Range("C12:O12")这类未指定工作表的引用会默认指向当前激活的"Email"表，因此正常执行。
• 但通过SheetChange事件触发时，触发事件的工作表不一定是"Email"（比如用户修改其他表内容也会触发），此时未指定工作表的单元格引用会指向触发事件的工作表，导致数据匹配错误；另外事件触发过程中工作表激活状态可能异常，也会引发报错。

解决方案 #

修改后的test子程序 #

给所有未指定工作表的单元格引用明确指定对象，同时禁用事件避免循环触发：

Sub test()
    Dim j As Integer
    Dim emailSheet As Worksheet
    Dim listsSheet As Worksheet
    
    ' 禁用事件，防止SheetChange循环触发
    Application.EnableEvents = False
    
    ' 提前绑定工作表对象，摆脱对ActiveSheet的依赖
    Set emailSheet = ThisWorkbook.Worksheets("Email")
    Set listsSheet = ThisWorkbook.Worksheets("Lists")
    
    j = 2
    ' 清空Lists表指定区域
    listsSheet.Range("Z2:Z31").Value = ""
    
    For i = 2 To 190
        ' 明确指定两个表的单元格，避免引用错误
        If listsSheet.Cells(i, 8).Value = emailSheet.Cells(2, 2).Value Then
            listsSheet.Cells(j, 26).Value = listsSheet.Cells(i, 6).Value
            j = j + 1
        End If
    Next
    
    ' 给Email表的区域设置自动筛选，明确指定工作表
    emailSheet.Range("C12:O12").AutoFilter Field:=1, _
        Criteria1:=">=" & emailSheet.Range("B1").Value, _
        Operator:=xlAnd, _
        Criteria2:="<=" & emailSheet.Range("C1").Value
    
    ' 恢复事件功能
    Application.EnableEvents = True
End Sub

额外优化 #

在事件触发代码中增加判断，只在"Email"表变更时执行test，减少不必要的运行：

Private Sub appevent_SheetChange(ByVal sh As Object, ByVal target As Range)
    ' 仅当Email表内容变更时才执行
    If sh.Name = "Email" Then
        Call test
    End If
End Sub

内容的提问来源于stack exchange，提问作者user11018142