excel - 如何根据excel中的另一个单元格值限制单元格中的值输入/输入？

Question

我有这样的东西


一个
乙


问？
是或否

数字
数字或零


我想做的是:
如果 B1 = “Y”，则让用户在 B2 中输入一个数字。
如果 B1 = “N”，则将 B2 中的值更改为零，并且不允许用户在 B2 中输入任何数字，除非用户将 B1 的值更改回“Y”。
基本上，当且仅当 B1=”Y” 时，才允许用户在 B2 中输入一个数字。
到目前为止我做了什么:

数据 -> 数据验证 -> 自定义，这是我写的公式

=OR(AND(B1="Y",ISNUMBER(B2)),AND(B1="N",B2=0))此解决方案并不理想 b/c 当 B1 被选为 N 时，它不会将 B2 更改为零。此外，当 B1 更改为 Y 时，它不会强制用户在 B2 中输入新值。

我尝试使用 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

此解决方案工作正常，除了当代码尝试再次锁定 B2 以防止用户直接将数字输入 B2 单元格(不使用输入框)​​时出现错误。另外，我需要添加一些代码来将数据输入限制为数字。
Error when changing the B1
知道我该如何解决这个问题吗？
PS:我设法在 SJR 的帮助下解决了上述代码

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

Answer 1

在这里使用样式可能会有所帮助。
创建一个名为“InputData”的样式，将locked设置为false(在 protected 选项卡(？)上)并将填充设置为例如黄色以将输入单元与其他单元区分开来。
将此样式分配给 B2
为 B2“问题”和 B3“答案”添加工作表名称。
将以下代码粘贴到工作表模块中:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If hasName(Target.Cells(1, 1)) Then
    If Target.Cells(1, 1).Name.Name Like "*Question*" Then
       lockUnlockAnswer
    End If
End If
End Sub


Private Sub lockUnlockAnswer()

Me.Unprotect

Application.EnableEvents = False

If Me.Range("Question") = "Y" Then
    With Me.Range("Answer")
        .Style = "InputData"  'this style
        .Value = vbNullString
    End With
Else
    With Me.Range("Answer")
        .Style = "Normal"
        .Value = 0
    End With
End If
    
Application.EnableEvents = True
    
Me.Protect
End Sub


Public Function hasName(ByVal c As Range) As Boolean
On Error Resume Next
hasName = CBool(c.Name.Name <> vbNullString)
If Err = 0 Then hasName = True
Err.Clear
On Error GoTo 0
End Function

每当用户更改工作表上的任何内容时，都会触发 on_change-event。
如果更改发生在“问题” lockUnlockAnswer叫做。
如果 Question = "Y"则 Style "InputData"应用于 B3 (= Answer)。单元格是可编辑的!
如果问题 <>“Y”，则样式“正常”应用于 B3。单元格被锁定并设置为 0。
为了防止再次调用 worksheet_change，EnableEvents 设置为 false。
(大概你会有更多应该像这样工作的问题/答案 - 你可以简单地为单元格名称添加一个索引。向 lockUnlockAnswer 添加一个参数“index”并将索引添加到范围名称......然后例程将适用于不同的问题/答案范围)