如何通过保护设置或VBA防止粘贴时修改工作表格式？

一、通过工作表保护设置实现（优先推荐） #

这种方法无需代码，通过权限限制阻止格式修改：

• 1. 解锁可编辑区域：选中整个工作表，右键→「设置单元格格式」→「保护」，取消勾选「锁定」，点击确定。
• 2. 锁定需保护格式的单元格（可选）：如果有部分单元格格式绝对不能修改，选中这些单元格，右键→「设置单元格格式」→「保护」，勾选「锁定」。
• 3. 开启工作表保护：点击「审阅」选项卡→「保护工作表」，在弹窗中取消勾选设置单元格格式、设置列格式、设置行格式等涉及格式修改的权限，仅保留「选定锁定单元格」「选定未锁定单元格」（如需允许插入行/列可额外勾选），设置保护密码后确定。
• 4. 引导用户粘贴值：此时用户直接粘贴会因权限限制报错，需引导他们使用「右键→粘贴值」或快捷键Ctrl+Alt+V选择「值」进行粘贴，确保仅内容更新、格式不受影响。

局限：用户需主动选择粘贴值，否则会触发权限错误提示，体验略有不足。

二、用VBA强制实现自动粘贴值 #

如果需要更流畅的体验，可通过VBA拦截粘贴操作，自动转换为仅粘贴值：

方案1：替换右键菜单的粘贴为「粘贴值」 #

将以下代码分别放入对应位置：

1. 工作表模块（双击工作表标签进入）：

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    ' 移除默认粘贴菜单
    On Error Resume Next
    CommandBars("Cell").Controls("粘贴(&P)").Delete
    On Error GoTo 0
    
    ' 添加自定义粘贴值按钮
    With CommandBars("Cell").Controls.Add(Type:=msoControlButton, Before:=1)
        .Caption = "粘贴值"
        .OnAction = "PasteValuesOnly"
        .FaceId = 22 ' 显示粘贴图标
    End With
End Sub

2. 标准模块（插入→模块）：

Sub PasteValuesOnly()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False ' 清除复制状态
End Sub

方案2：监听粘贴事件自动转换 #

将代码放入工作表模块：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim undoAction As String
    undoAction = Application.CommandBars("Standard").Controls("撤销").List(1)
    
    ' 判断是否为粘贴操作
    If Left(undoAction, 5) = "粘贴" Then
        Application.EnableEvents = False ' 避免循环触发
        Application.Undo ' 撤销原粘贴（带格式）
        Target.PasteSpecial Paste:=xlPasteValues ' 重新粘贴值
        Application.EnableEvents = True
    End If
End Sub

注意：使用VBA需将文件保存为.xlsm格式，且启用宏才能生效。

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