受保护工作表中Worksheet_Change下拉多选VBA失效求解

代码失效是两个问题共同导致的：

• 常规工作表保护会同时限制用户手动操作和VBA的单元格写入权限，你之前插入的取消/重保护逻辑，因为代码本身存在语法错误、缺少容错，触发错误后直接跳转到退出分支，根本没执行到解保或后续写入逻辑
• 原代码存在冗余的错误分支判断，SpecialCells方法在工作表保护状态下无前置容错会直接抛错，中断整个事件执行

1. 配置工作表保护权限（核心优化，无需反复解保） #

不需要在事件代码里反复写取消保护、重新保护的逻辑，直接利用Excel保护的UserInterfaceOnly参数，实现「仅限制用户手动编辑，VBA操作完全不受限」的效果，安全性更高，不会出现代码崩溃导致工作表脱保的问题。
按Alt+F11打开VBA编辑器，在左侧工程栏双击ThisWorkbook模块，粘贴以下代码，将工作表名称、保护密码替换为你实际使用的值：

Private Sub Workbook_Open()
    ' 替换为你的实际工作表名、保护密码
    Sheets("协作表").Protect Password:="your_password", UserInterfaceOnly:=True
End Sub

该代码会在每次打开工作簿时自动应用带权限的保护，无需手动重复设置。

2. 替换原有Worksheet_Change事件代码 #

原代码存在分支语法错误、容错缺失问题，直接替换为修正后的版本即可：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    
    ' 非目标单元格直接退出过程
    If Target.Address <> "$K$9" Then Exit Sub
    
    ' 前置容错：判断目标单元格是否存在数据验证，避免SpecialCells抛错
    On Error Resume Next
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub
    On Error GoTo Exitsub
    
    ' 单元格为空时不执行追加逻辑
    If Target.Value = "" Then GoTo Exitsub
    
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    
    If Oldvalue = "" Then
        Target.Value = Newvalue
    Else
        ' 选项不存在重复时才追加，重复则保留原值
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
        Else
            Target.Value = Oldvalue
        End If
    End If

Exitsub:
    ' 无论过程是否报错，都恢复事件触发
    Application.EnableEvents = True
End Sub

如果不想使用UserInterfaceOnly配置，可以在事件代码最开头添加解保语句，在退出标签前添加重保护语句：

• 过程开头添加：Me.Unprotect Password:="your_password"
• Exitsub标签前、Application.EnableEvents = True语句后添加：Me.Protect Password:="your_password"
  注意：该方案如果代码执行中途出现崩溃（比如Excel强制退出），会导致工作表处于未保护状态，存在误改风险。

• 所有代码部署完成后，将文件保存为.xlsm启用宏的工作簿格式
• 打开文件时需要启用宏才能正常使用多选功能
• 如果需要调整多选生效的单元格，直接修改代码中$K$9为对应的目标单元格地址即可

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