VBA运行时错误1004：保护工作表后Range/Cell操作报错

首先，咱们来拆解你遇到的问题根源，然后一步步解决：

核心错误原因 #

1. 事件重入导致保护状态下操作单元格：当你的代码修改单元格值（比如Range("T8:T" & lastrow) = Range("U5")）时，会再次触发Worksheet_Change事件。如果第一次事件已经执行了重新保护工作表的操作，第二次事件执行时工作表处于保护状态，操作Range/Cell就会抛出1004错误。
2. 工作表保护未设置UserInterfaceOnly参数：默认的Protect方法会限制所有操作（包括VBA代码），如果不指定UserInterfaceOnly:=True，VBA即使在代码里操作单元格也会被拦截。
3. 代码逻辑的小问题：变量声明不规范、跨工作表的Range相交判断错误、未指定工作表的Cells引用，这些都会导致意外错误。

分步解决方案 #

1. 修正事件所在的工作表（关键！） #

你的代码里判断的是Sheets(2)的U5单元格变化，但如果这个Worksheet_Change事件是放在其他工作表的代码模块里，Target是当前工作表的单元格，和Sheets(2)的KeyCells永远不会相交，代码根本不会执行内部逻辑。

→ 把这个事件代码移动到Sheets(2)的代码模块中（右键点击Sheet2标签→查看代码，粘贴进去）。

2. 禁用事件重入+错误处理 #

在代码开头禁用事件触发，防止修改单元格时重复触发事件；同时添加错误处理，确保即使代码出错，事件也能恢复启用，避免后续Excel操作异常。

3. 使用UserInterfaceOnly参数保护工作表 #

设置这个参数后，VBA代码可以不受保护限制操作单元格，而用户界面依然保持保护状态。注意：这个参数在Excel重启后会失效，所以最好在Workbook_Open事件中也重新设置一次。

4. 修正代码中的变量和引用问题 #

• 规范变量声明（避免Variant类型隐式声明）
• 确保所有Range/Cells都指定了Sheets(2)（避免默认引用ActiveSheet）

修改后的完整代码 #

Sheets(2)的Worksheet_Change事件代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 禁用事件防止重入
    Application.EnableEvents = False
    ' 错误处理，确保事件能恢复
    On Error GoTo Cleanup

    Dim KeyCells As Range
    Dim lastrow As Long, i As Long

    With Me ' 这里用Me指代当前工作表（即Sheets(2)），比Sheets(2)更灵活
        ' 如果已经通过UserInterfaceOnly保护，这里可以不用Unprotect，不过保险起见保留
        .Unprotect Password:="xyz"

        Set KeyCells = .Range("U5")
        ' 判断当前修改的单元格是否是U5
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            ' 调用FindLastRow时指定当前工作表
            lastrow = FindLastRow(Me, "*")
            
            ' 修改T列数据，确保引用当前工作表
            .Range("T8:T" & lastrow).Value = .Range("U5").Value

            ' 遍历行计算U列值，所有Cells都加上.前缀，指定当前工作表
            For i = 8 To lastrow
                If .Cells(i, "R").Value > .Cells(i, "T").Value Then
                    .Cells(i, "U").Value2 = IIf(.Cells(i, "R").Value > .Cells(i, "S").Value, _
                                               .Cells(i, "R").Value, .Cells(i, "S").Value)
                Else
                    .Cells(i, "U").Value2 = IIf(.Cells(i, "T").Value > .Cells(i, "S").Value, _
                                               .Cells(i, "T").Value, .Cells(i, "S").Value)
                End If
            Next i
        End If

        ' 重新保护工作表，设置UserInterfaceOnly:=True
        .Protect Password:="xyz", UserInterfaceOnly:=True
    End With

Cleanup:
    ' 恢复事件触发
    Application.EnableEvents = True
    ' 如果有错误，抛出错误信息（可选）
    If Err.Number <> 0 Then
        MsgBox "错误：" & Err.Description, vbExclamation
    End If
End Sub

修正FindLastRow函数（确保指定工作表） #

你的FindLastRow函数需要接收工作表参数，避免默认引用ActiveSheet：

Function FindLastRow(ByVal ws As Worksheet, ByVal searchColumn As String) As Long
    ' 在指定工作表的指定列查找最后一行
    Dim lastCell As Range
    Set lastCell = ws.Cells(ws.Rows.Count, searchColumn).End(xlUp)
    FindLastRow = lastCell.Row
End Function

补充Workbook_Open事件（恢复UserInterfaceOnly参数） #

右键点击ThisWorkbook→查看代码，添加以下代码，确保Excel重启后依然保持VBA操作权限：

Private Sub Workbook_Open()
    ' 重新设置Sheets(2)的保护，启用UserInterfaceOnly
    With Sheets(2)
        If .ProtectContents Then
            .Unprotect Password:="xyz"
        End If
        .Protect Password:="xyz", UserInterfaceOnly:=True
    End With
End Sub

额外说明 #

• 如果你不需要用户界面保护某些单元格，可以提前解锁这些单元格（选中单元格→右键→设置单元格格式→保护→取消勾选锁定），然后再保护工作表，这样用户也能编辑这些单元格，VBA操作也不受影响。
• 永远在修改单元格的VBA代码中禁用事件触发，避免无限循环或意外的事件重入问题。

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