Excel VBA数据验证下拉列表启停的运行时错误排查

问题根源 #

你遇到的1004错误，是因为Excel不支持直接通过设置.InCellDropdown = False来关闭已存在的数据验证下拉列表。若要实现允许自由输入的需求，正确的操作是删除目标单元格的数据验证规则，而非修改属性。

修复后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetCell As Range
    Dim validationCell As Range
    
    ' 处理多单元格选中的情况
    If Target.Count > 1 Then GoTo ExitSub
    
    Set targetCell = Intersect(Target, Range("O16:O615"))
    If Not targetCell Is Nothing Then
        Set validationCell = Cells(targetCell.Row, targetCell.Column + 1)
        
        Select Case targetCell.Value
            Case "Unverified"
                ' 先清除旧验证，避免属性冲突
                On Error Resume Next
                validationCell.Validation.Delete
                On Error GoTo 0
                
                ' 重新创建数据验证（替换Formula1为你的实际下拉数据源）
                With validationCell.Validation
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                         Operator:=xlBetween, Formula1:="Option1,Option2,Option3"
                    .InCellDropdown = True
                    .ShowError = True
                    .ErrorMessage = "请选择下拉列表中的选项"
                End With
                
            Case "Yes", "No"
                ' 删除验证规则，允许自由输入
                On Error Resume Next
                validationCell.Validation.Delete
                On Error GoTo 0
        End Select
    End If
    
ExitSub:
End Sub

关键改动说明 #

1. 删除验证规则替代属性修改：选择"Yes"/"No"时直接删除数据验证，彻底解除输入限制，避免了原代码中修改属性触发的错误
2. 重建验证规则：选择"Unverified"时先清除旧验证再重建，确保下拉列表和错误提示正常生效
3. 错误处理：添加On Error Resume Next防止因单元格无验证规则时，删除操作触发额外错误

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