Excel Worksheet_Change事件无法调用对应模块的排查求助

我一直尝试实现Excel工作表检测到变更时调用对应模块，但始终无法成功。已知变更可被检测到（变更触发时会弹出错误），以下是我的Worksheet_Change事件代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "Worksheet_Change event fired"
    Dim row As Long
    
    'Check if the changed cells are in column D or F and have been filled
    If Target.Column = 4 Or Target.Column = 6 Then
        If Target.Value <> "" And Cells(Target.row, Target.Column - 1).Value <> "" Then
            'Both cells in the row have been filled, ask the user if they want to run the script
            row = Target.row
            If MsgBox("Do you want to calculate the hours for " & row & "?", vbYesNo) = vbYes Then
                'User clicked Yes, execute the script for the row
                Select Case row
                    Case 10
                        Call Monday
                        MsgBox "Monday has been calculated"
                        
                    Case 11
                        Call Tuesday
                        MsgBox "Tuesday has been calculated"
                        
                    Case 12
                        Call Wednesday
                        MsgBox "Wednesday has been calculated"
                        
                    Case 13
                        Call Thrusday
                        MsgBox "Thursday has been calculated"
                        
                    Case 14
                        Call Friday
                        MsgBox "Friday has been calculated"
                End Select
            Else
                'User clicked No, move to the next row (if it exists)
                If row < 14 Then
                    'Move to the next row
                    Cells(row + 1, 4).Select
                End If
            End If
        End If
    End If
End Sub

所有待调用的模块均为Public类型，示例如下：

Public Sub Monday()
   'script here
End Sub

我已尝试多种方法，包括使用Application.Run、指定模块名、改用Worksheet_Calculate事件并重写脚本，但均无效果。手动运行模块一切正常，也尝试过Call Monday.Monday的写法。

更新：已修复错误，但模块仍未执行，同时更新了脚本以适配合并单元格。

解决建议 #

1. 禁用事件防止递归触发 #

调用模块前先禁用Excel事件，避免模块执行时修改单元格再次触发Worksheet_Change，导致逻辑中断：

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "Worksheet_Change event fired"
    Dim row As Long
    
    ' 禁用事件防止递归
    Application.EnableEvents = False
    
    ' 原逻辑代码保持不变
    If Target.Column = 4 Or Target.Column = 6 Then
        If Target.Value <> "" And Cells(Target.row, Target.Column - 1).Value <> "" Then
            row = Target.row
            If MsgBox("Do you want to calculate the hours for " & row & "?", vbYesNo) = vbYes Then
                Select Case row
                    Case 10
                        Call Monday
                        MsgBox "Monday has been calculated"
                    Case 11
                        Call Tuesday
                        MsgBox "Tuesday has been calculated"
                    Case 12
                        Call Wednesday
                        MsgBox "Wednesday has been calculated"
                    Case 13
                        Call Thrusday
                        MsgBox "Thursday has been calculated"
                    Case 14
                        Call Friday
                        MsgBox "Friday has been calculated"
                End Select
            Else
                If row < 14 Then
                    Cells(row + 1, 4).Select
                End If
            End If
        End If
    End If
    
    ' 恢复事件
    Application.EnableEvents = True
End Sub

2. 明确指定模块作用域 #

如果模块存储在独立的标准模块中，调用时需明确指定模块名称（假设模块名为WeekdayCalculations）：

' 替换原Call语句
Call WeekdayCalculations.Monday
' 或使用Application.Run
Application.Run "WeekdayCalculations.Monday"

3. 适配合并单元格的Target范围 #

合并单元格的Target是整个合并区域，需取左上角单元格的列号判断：

Dim targetCol As Long
targetCol = Target.Cells(1, 1).Column
If targetCol = 4 Or targetCol = 6 Then
    ' 后续逻辑
End If

4. 检查宏安全设置 #

确认Excel宏设置未阻止模块执行：

• 打开Excel选项 → 信任中心 → 信任中心设置 → 宏设置 → 临时选择"启用所有宏"测试（正式环境建议使用数字签名或受信任位置）

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