Workbook_SheetChange事件二次触发报错问题求助

1. 单元格引用的父对象不匹配 #

报错的直接原因是ThisWorkbook.Sheets("str1").Range(Cells(29, 15), Cells(42, 15))这段代码中，Cells(29,15)和Cells(42,15)未指定所属工作表，默认会引用当前活动工作表的单元格。第二次触发Workbook_SheetChange时，活动工作表已经不是str1（比如CheckIDH里激活了乱码表ñòð1或Dic表），此时用其他工作表的单元格去构建str1的Range，就会出现“范围不存在”的错误。

2. 事件递归触发 #

CheckIDH中执行Sheets("str1").Cells(x.Row, 1) = Dic(CStr(x)).Desc会修改工作表内容，再次触发Workbook_SheetChange事件，形成递归调用，放大了第一个问题的影响。

步骤1：修复Workbook_SheetChange事件代码 #

明确指定单元格的父对象，同时关闭事件避免递归：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim str1Sheet As Worksheet
    Dim watchRange As Range
    
    Set str1Sheet = ThisWorkbook.Sheets("str1")
    ' 强制Cells属于str1Sheet，避免依赖活动表
    Set watchRange = str1Sheet.Range(str1Sheet.Cells(29, 15), str1Sheet.Cells(42, 15))
    
    If Not Intersect(Target, watchRange) Is Nothing Then
        ' 关闭事件防止递归触发
        Application.EnableEvents = False
        Call CheckIDH(Target)
        ' 恢复事件
        Application.EnableEvents = True
    End If
    
    Set str1Sheet = Nothing
    Set watchRange = Nothing
End Sub

步骤2：修正模块中的代码问题 #

• 移除无效的Set Target = Nothing（Target是事件参数，非全局变量，赋值无效）
• 替换乱码工作表名ñòð1为正确的str1
• 给所有Cells调用指定父对象，避免依赖活动表：

Public count As Long
Public Dic As New Collection

Sub CheckIDH(ByVal TRange As Range)
    Dim str1Sheet As Worksheet
    Set str1Sheet = ThisWorkbook.Sheets("str1")
    
    If Dic.count = 0 Then
        Call Fill_Dic
    End If
    
    For Each x In TRange
        If Exists(CStr(x.Value), Dic) = True Then
            str1Sheet.Cells(x.Row, 1) = Dic(CStr(x.Value)).Desc
        End If
    Next x
    
    Set str1Sheet = Nothing
End Sub

Sub Fill_Dic()
    Dim dicSheet As Worksheet
    Set dicSheet = ThisWorkbook.Sheets("Dic")
    
    count = 0
    Dic.Clear ' 清空旧数据避免重复添加
    Do While dicSheet.Cells(count + 1, 1).Value <> ""
        count = count + 1
        If count > 1 And Exists(CStr(dicSheet.Cells(count, 1).Value), Dic) = False Then
            Dic.Add Item:=New IDH, Key:=CStr(dicSheet.Cells(count, 1).Value)
            Dic(CStr(dicSheet.Cells(count, 1).Value)).IDHNbr = dicSheet.Cells(count, 1).Value
            Dic(CStr(dicSheet.Cells(count, 1).Value)).Desc = dicSheet.Cells(count, 2).Value
        End If
    Loop
    
    Set dicSheet = Nothing
End Sub

Function Exists(Key As String, Col As Collection) As Boolean
    On Error Resume Next
    Exists = Not IsEmpty(Col.Item(Key))
    Err.Clear
End Function

额外注意事项 #

• 确保IDH类模块已正确创建，包含IDHNbr和Desc两个公共属性
• 避免使用Activate切换工作表，直接通过工作表对象操作更稳定高效

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