VBA宏按钮执行异常，单步调试正常的问题排查与修复

你遇到的问题核心是代码依赖当前激活的工作表，单步调试时你手动控制了工作表上下文，所以一切正常，但通过按钮运行时，宏会默认使用当前处于激活状态的工作表，导致Range引用错位，出现“错误工作表弹出提示”这类异常。

主要问题点： #

• 未明确限定Range/Cells的所属工作表（比如Range("E7", "DL7")这种写法默认指向当前激活表）
• 不必要的Sheets("Reyestr").Activate操作，会干扰代码的上下文环境

修复后的完整代码 #

Option Explicit
Public MainID As String
Public ReyestrID As String
Public RN As Long
Public Cell As Range
Public bIsEmpty As Boolean

Sub CopyPasteFirstRow()
    Call Unprotect
    
    Dim WsMain As Worksheet
    Dim WsReyestr As Worksheet
    Set WsMain = ThisWorkbook.Sheets("Main")
    Set WsReyestr = ThisWorkbook.Sheets("Reyestr")
    
    MainID = WsMain.Range("CS7").Value
    ReyestrID = WsReyestr.Cells(WsReyestr.Rows.Count, 97).End(xlUp).Value
    bIsEmpty = False
    
    If MainID = ReyestrID Then
        MsgBox "This content has been already added!", vbCritical
    Else
        ' 遍历Main表的指定单元格，明确限定工作表
        For Each Cell In WsMain.Range("E7:F7,H7:R7,T7,AA7")
            If IsEmpty(Cell) = True Then
                bIsEmpty = True
                Exit For
            End If
        Next Cell
        
        If bIsEmpty = True Then
            MsgBox "Please enter all necessary info", vbCritical
        Else
            ' 直接限定工作表复制，无需激活
            WsMain.Range("E7:DL7").Copy
            With WsReyestr.Cells(WsReyestr.Rows.Count, 5).End(xlUp).Offset(1, 0)
                .PasteSpecial
                .PasteSpecial xlPasteValuesAndNumberFormats
            End With
            Application.CutCopyMode = False ' 清除剪贴板状态
        End If
    End If
    Call Protect
End Sub

Sub DeleteLastInformationInReyestr()
    Call Unprotect
    
    Dim WsMain As Worksheet
    Dim WsReyestr As Worksheet
    Set WsMain = ThisWorkbook.Sheets("Main")
    Set WsReyestr = ThisWorkbook.Sheets("Reyestr")
    
    MainID = WsMain.Range("CS7").Value
    ReyestrID = WsReyestr.Cells(WsReyestr.Rows.Count, 97).End(xlUp).Value
    RN = WsReyestr.Cells(WsReyestr.Rows.Count, 5).End(xlUp).Row
    
    If MainID = ReyestrID Then
        ' 直接限定工作表清除内容，无需激活
        WsReyestr.Range("E" & RN & ":DL" & RN).Clear
    Else
        MsgBox "You can clear only last added content!", vbCritical
    End If
    Call Protect
End Sub

Sub DeleteInsertedInfoFromMain()
    Call Unprotect
    
    Dim Answer As Integer
    Dim WsReyestr As Worksheet
    Dim WsMain As Worksheet
    Set WsReyestr = ThisWorkbook.Sheets("Reyestr")
    Set WsMain = ThisWorkbook.Sheets("Main")
    
    MainID = WsMain.Range("CS7").Value
    
    If Application.WorksheetFunction.CountIf(WsReyestr.Range("CS:CS"), MainID) > 0 Then
        WsMain.Range("E7:Z7,AD7:AV7,BD7:BE7,BH7:BJ7,BO7:CM7,CQ7").ClearContents
    Else
        Answer = MsgBox("Yuxarida qeyd olunan melumat reyestre elave olunbayib! Melumati reyestre daxil etmeden silmek isteyirsiz?", vbQuestion + vbYesNo + vbDefaultButton2, "Diqqetli olun!")
        If Answer = vbYes Then
            WsMain.Range("E7:Z7,AD7:AV7,BD7:BE7,BH7:BJ7,BO7:CM7,CQ7").ClearContents
        Else
            Exit Sub
        End If
    End If
    Call Protect
End Sub

Sub Protect()
    ' 增加错误捕获，避免密码错误或表不存在时崩溃
    On Error Resume Next
    With ThisWorkbook.Sheets("Main")
        .Protect "PassABC", userinterfaceonly:=True
        .EnableSelection = xlNoRestrictions
    End With
    With ThisWorkbook.Sheets("Reyestr")
        .Protect "PassABC", userinterfaceonly:=True
        .EnableSelection = xlNoRestrictions
    End With
    On Error GoTo 0
End Sub

Sub Unprotect()
    ' 增加错误捕获
    On Error Resume Next
    ThisWorkbook.Sheets("Main").Unprotect "PassABC"
    ThisWorkbook.Sheets("Reyestr").Unprotect "PassABC"
    On Error GoTo 0
End Sub

关键修改说明： #

1. 明确限定工作表：所有Range/Cells操作都通过WsMain或WsReyestr对象调用，彻底摆脱对当前激活工作表的依赖
2. 移除不必要的Activate操作：激活工作表不仅会拖慢宏的运行速度，还容易引发上下文错误，直接通过工作表对象操作更可靠
3. 优化复制粘贴逻辑：使用With语句简化代码，添加Application.CutCopyMode = False清除剪贴板状态
4. 增加错误捕获：在Protect和Unprotect子过程中添加错误处理，避免因密码错误、工作表不存在等意外情况导致宏崩溃

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