Excel受保护工作表执行VBA后下拉菜单失效问题求助

问题原因 #

手动设置工作表保护时，Excel默认启用了数据验证下拉菜单的交互权限，但VBA的Protect方法默认参数会禁用该权限，导致执行代码后下拉菜单无法正常使用。

修复方案 #

1. 更新CommandButton的VBA代码 #

修改Protect方法的参数，明确开启用户界面操作权限及数据验证相关权限：

Private Sub CommandButton1_Click()
    With ActiveSheet
        .Unprotect Password:="password"
        .Range("a1").Value = "prova"
        ' 关键参数：UserInterfaceOnly让VBA可直接操作保护表，AllowFiltering确保下拉菜单可用
        .Protect Password:="password", _
                 DrawingObjects:=True, _
                 UserInterfaceOnly:=True, _
                 AllowFiltering:=True
    End With
    Unload Me
End Sub

说明：

• UserInterfaceOnly:=True：允许VBA代码无需解锁即可操作工作表，同时保持用户界面的保护状态
• AllowFiltering:=True：启用数据验证下拉菜单的交互权限（数据验证依赖该权限）

2. 实现双击下拉单元格调出Userform1 #

右键目标工作表标签→选择「查看代码」，在工作表代码模块中添加以下事件代码：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 仅当双击的单元格包含数据验证（下拉菜单）时触发
    If Not Target.Validation Is Nothing Then
        UserForm1.Show
        Cancel = True ' 取消默认双击编辑行为，避免进入单元格编辑模式
    End If
End Sub

3. 补充优化（可选） #

如果Excel重启后UserInterfaceOnly权限失效，可在工作簿打开事件中重新应用保护：

Private Sub Workbook_Open()
    With ThisWorkbook.Sheets("你的工作表名称") ' 替换为实际工作表名
        .Protect Password:="password", _
                 DrawingObjects:=True, _
                 UserInterfaceOnly:=True, _
                 AllowFiltering:=True
    End With
End Sub

若下拉菜单是通过窗体控件（而非数据验证）实现的，需在Protect参数中添加AllowUsingFormControls:=True

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