Excel中如何根据另一单元格的值限制指定单元格的输入内容

我的表格结构如下：

预期实现效果 #

• 若B1单元格值为“Y”，允许用户在B2单元格输入数字
• 若B1值为“N”，自动将B2值设为0，且禁止用户在B2输入任何内容，直到B1改回“Y”

核心规则：仅当B1值为“Y”时，用户才可以在B2输入数字。

现有尝试及问题 #

方案1：数据验证 #

操作路径：【数据】→【数据验证】→【自定义】，使用公式：
=OR(AND(B1="Y",ISNUMBER(B2)),AND(B1="N",B2=0))
存在问题：

• B1选择为N时不会自动将B2设为0
• B1改回Y时不会强制用户重新输入B2的值

方案2：初始VBA代码 #

代码如下：

ActiveSheet.Unprotect
Range("B2").Locked = False
If Target.Address = "$B$1" Then
    Dim myValue As Variant
    If Range("B1").Value = "N" Then
        Range("B2").Value = 0
    Else
        myValue = InputBox("Give me some input")
        Range("B2").Value = myValue
    
    End If
    
End If
Range("B2").Locked = True
ActiveSheet.Protect
End Sub

存在问题：

1. 代码尝试重新锁定B2以禁止用户直接在单元格输入内容时会报错
2. 没有限制B2仅可输入数字

优化后的VBA代码（仍存在问题） #

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("Sheet1").Unprotect

If Target.Address = "$B$1" Then
    Dim myValue As Variant
    If Range("B1").Value = "N" Then
        Range("B2").Value = 0
    Else
    myValue = "no"
        Do Until IsNumeric(myValue)
                myValue = InputBox("Enter a number > 0")
        Loop
        Range("B2").Value = myValue
    End If
    
End If
Worksheets("Sheet1").Protect
End Sub

报错核心原因是修改B2单元格值时会触发Worksheet_Change事件递归调用，导致重复执行保护/解锁逻辑冲突，另外需要补充取消输入的容错处理，最终可用代码如下：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 关闭事件触发，避免修改B2时递归调用本过程
    Application.EnableEvents = False
    ' 解锁工作表，如有设置密码则填在括号内，例：Me.Unprotect("123456")
    Me.Unprotect
    
    If Target.Address = "$B$1" Then
        Dim myValue As Variant
        If Range("B1").Value = "N" Then
            Range("B2").Value = 0
            ' 锁定B2禁止手动编辑
            Range("B2").Locked = True
        Else
            ' 解锁B2允许编辑
            Range("B2").Locked = False
            myValue = ""
            Do
                myValue = InputBox("请输入B2的数字值")
                ' 处理用户点击取消的情况
                If myValue = "" Then
                    MsgBox "必须输入有效数字"
                ElseIf Not IsNumeric(myValue) Then
                    MsgBox "输入内容不是有效数字，请重新输入"
                Else
                    ' 如需限制数字大于0，可在此处补充判断逻辑
                    Exit Do
                End If
            Loop
            Range("B2").Value = myValue
        End If
    End If
    
    ' 重新保护工作表，UserInterfaceOnly参数允许VBA修改单元格，避免后续操作报错
    Me.Protect UserInterfaceOnly:=True
    ' 恢复事件触发
    Application.EnableEvents = True
End Sub

部署说明 #

1. 首次部署代码前，手动选中B2单元格，右键→设置单元格格式→保护，确认「锁定」选项已勾选
2. 首次运行前手动给工作表设置保护，保证锁定状态生效
3. 如不需要弹出输入框，仅希望B1为Y时用户直接在B2编辑，可删除输入框相关逻辑，仅保留锁定/解锁和赋值逻辑即可

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