锁定公式的VBA代码影响其他宏运行及触发1004错误求助

问题根源分析 #

原代码的核心问题在于调用.Protect时未启用UserInterfaceOnly参数，导致VBA代码和用户操作都被限制；同时未处理无公式工作表的异常场景，引发潜在错误。

修改后的VBA代码 #

Sub LockSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws
            .Unprotect '先解除现有保护
            .Cells.Locked = False '默认所有单元格设为可编辑
            '处理无公式单元格的异常情况
            On Error Resume Next
            .Cells.SpecialCells(xlCellTypeFormulas).Locked = True '仅锁定公式单元格
            On Error GoTo 0 '恢复默认错误处理机制
            
            '保护工作表：仅限制用户界面操作，允许VBA代码修改
            .Protect Password:="", UserInterfaceOnly:=True, _
                AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, AllowInsertingColumns:=True, _
                AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
                AllowDeletingColumns:=True, AllowDeletingRows:=True, _
                AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
        End With
    Next ws
End Sub

关键改进点说明 #

• UserInterfaceOnly:=True：这是解决问题的核心，启用后仅限制用户手动编辑操作，VBA代码可正常修改工作表内容，彻底解决插入图片不更新、代码操作单元格触发1004错误的问题。
• 错误处理机制：通过On Error Resume Next和On Error GoTo 0，避免因工作表无公式单元格导致的运行时错误。
• 自定义保护权限：代码中开放了常用的用户操作权限（如格式设置、插入行/列等），你可根据实际需求增删参数（比如不需要允许删除行，就去掉AllowDeletingRows:=True）。

额外优化建议 #

由于UserInterfaceOnly设置在工作簿关闭后会失效，可将保护代码加入工作簿打开事件，实现自动启用保护：

1. 打开VBA编辑器，双击左侧的ThisWorkbook模块
2. 粘贴以下代码：

Private Sub Workbook_Open()
    LockSheets
End Sub

3. 若需设置保护密码，将.Protect中的Password:=""替换为你的密码（如Password:="123456"）

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