excel - #VBA - 私有(private)子 Worksheet_Change 错误 : Method 'Hidden' of Object 'Range' Field

Question

花了三个多小时试图解决这个问题。任何帮助，将不胜感激。我在底部尝试过的解决方案。
问题:
我正在尝试更新代码，以便如果有人在单元格 E58 中选择“NA”(此单元格上有 3 个下拉选项:是/否/NA)，它会将单元格 E60 和 E65 的值更新为 NA同样，在不破坏当前行动的情况下。我尝试过的每个解决方案都会导致代码循环回到顶部并在顶部的第 6 行失败和/或使文件崩溃:

   Rows("9").EntireRow.Hidden = True

错误消息是:运行时错误，对象“范围”字段的方法“隐藏”
代码如下:

Private Sub Worksheet_Change(ByVal Target As Range)   

If Range("E8").Value = "No" Then
    Rows("9").EntireRow.Hidden = True
ElseIf Range("E8").Value = "Yes" Then
    Rows("9").EntireRow.Hidden = False
    ElseIf Range("E8").Value = "" Then
    Rows("9").EntireRow.Hidden = True

End If

 If Range("E10").Value = "No" Then
    Rows("11").EntireRow.Hidden = True
ElseIf Range("E10").Value = "No" Then
    Rows("11").EntireRow.Hidden = False
ElseIf Range("E10").Value = "Yes" Then
    Rows("11").EntireRow.Hidden = True
     ElseIf Range("E10").Value = "" Then
    Rows("11").EntireRow.Hidden = True

End If

If Range("E58").Value = "Yes" Then
    Rows("59").EntireRow.Hidden = True
     
ElseIf Range("E58").Value = "NA" Then
    Rows("59").EntireRow.Hidden = True
    Range("E60").Value = "NA"
ElseIf Range("E58").Value = "No" Then
    Rows("59").EntireRow.Hidden = False
 ElseIf Range("E58").Value = "" Then
    Rows("59").EntireRow.Hidden = True

End If


 
 If Range("E60").Value = "No" Then
    Rows("61").EntireRow.Hidden = True
    Rows("62").EntireRow.Hidden = False
    Rows("63").EntireRow.Hidden = True
ElseIf Range("E60").Value = "NA" Then
    Rows("61:62").EntireRow.Hidden = True
ElseIf Range("E60").Value = "Yes" Then
    Rows("62").EntireRow.Hidden = False
    Rows("63").EntireRow.Hidden = False
    Rows("61").EntireRow.Hidden = True
ElseIf Range("E60").Value = "" Then
    Rows("61:63").EntireRow.Hidden = True
End If



If Range("E63").Value = "No" Then
    Rows("64").EntireRow.Hidden = False
ElseIf Range("E63").Value = "N/A" Then
    Rows("64").EntireRow.Hidden = True
ElseIf Range("E63").Value = "Yes" Then
    Rows("64").EntireRow.Hidden = True
ElseIf Range("E63").Value = "Partial" Then
    Rows("64").EntireRow.Hidden = False
ElseIf Range("E63").Value = "" Then
    Rows("64").EntireRow.Hidden = True
End If
 
 
 If Range("E65").Value = "False" Then
    Rows("66").EntireRow.Hidden = True
    Rows("67").EntireRow.Hidden = True
ElseIf Range("E65").Value = "NA" Then
    Rows("66:67").EntireRow.Hidden = True
ElseIf Range("E65").Value = "Yes" Then
    Rows("66").EntireRow.Hidden = False
    Rows("67").EntireRow.Hidden = False
ElseIf Range("E65").Value = "" Then
    Rows("66:67").EntireRow.Hidden = True
End If

注意:对于未提及的行有类似的命令，但仅在此处需要此操作。
尝试的解决方案:

尝试在 ELSEIF 语句中添加一行

  ElseIf Range("E58").Value = "NA" Then
  Range("E60").Value = "NA"

虽然这确实在 E60 中吐出了 NA，但代码循环回到顶部并再次在第 6 行失败。

尝试添加一个与上述相同的公共(public)子，调用此公共(public)子与 Range("E60").Value = "NA"语句放置在同一位置，但它在同一位置失败和/或崩溃优秀的。

尝试使用 Application.EnableEvents = True/False 进行试验，但这会导致 ELSEIF 语句( ElseIf Range("E58").Value = ""Then
Rows("59").EntireRow.Hidden = True) 无法正常运行。

帮助将不胜感激。
谢谢你。

Answer 1

您必须添加 Application.EnableEvents = False在 Private Sub Worksheet_Change(ByVal Target As Range) 之后和 Application.EnableEvents = True之前 End Sub .
由于您试图更改同一工作表上的单元格值，Worksheet_Change当您更改同一张工作表上的单元格值时，事件将自行触发，从而导致 无限循环 .
修改后的代码将如下所示:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Range("E8").Value = "No" Then
        Rows("9").EntireRow.Hidden = True
    ElseIf Range("E8").Value = "Yes" Then
        Rows("9").EntireRow.Hidden = False
    ElseIf Range("E8").Value = "" Then
        Rows("9").EntireRow.Hidden = True
    End If

    If Range("E10").Value = "No" Then
        Rows("11").EntireRow.Hidden = True
    ElseIf Range("E10").Value = "No" Then
        Rows("11").EntireRow.Hidden = False
    ElseIf Range("E10").Value = "Yes" Then
        Rows("11").EntireRow.Hidden = True
    ElseIf Range("E10").Value = "" Then
        Rows("11").EntireRow.Hidden = True
    End If

    If Range("E58").Value = "Yes" Then
        Rows("59").EntireRow.Hidden = True
    ElseIf Range("E58").Value = "NA" Then
        Rows("59").EntireRow.Hidden = True
        Range("E60").Value = "NA"
    ElseIf Range("E58").Value = "No" Then
        Rows("59").EntireRow.Hidden = False
    ElseIf Range("E58").Value = "" Then
        Rows("59").EntireRow.Hidden = True
    End If
 
    If Range("E60").Value = "No" Then
        Rows("61").EntireRow.Hidden = True
        Rows("62").EntireRow.Hidden = False
        Rows("63").EntireRow.Hidden = True
    ElseIf Range("E60").Value = "NA" Then
        Rows("61:62").EntireRow.Hidden = True
    ElseIf Range("E60").Value = "Yes" Then
        Rows("62").EntireRow.Hidden = False
        Rows("63").EntireRow.Hidden = False
        Rows("61").EntireRow.Hidden = True
    ElseIf Range("E60").Value = "" Then
        Rows("61:63").EntireRow.Hidden = True
    End If

    If Range("E63").Value = "No" Then
        Rows("64").EntireRow.Hidden = False
    ElseIf Range("E63").Value = "N/A" Then
        Rows("64").EntireRow.Hidden = True
    ElseIf Range("E63").Value = "Yes" Then
        Rows("64").EntireRow.Hidden = True
    ElseIf Range("E63").Value = "Partial" Then
        Rows("64").EntireRow.Hidden = False
    ElseIf Range("E63").Value = "" Then
        Rows("64").EntireRow.Hidden = True
    End If
 
    If Range("E65").Value = "False" Then
        Rows("66").EntireRow.Hidden = True
        Rows("67").EntireRow.Hidden = True
    ElseIf Range("E65").Value = "NA" Then
        Rows("66:67").EntireRow.Hidden = True
    ElseIf Range("E65").Value = "Yes" Then
        Rows("66").EntireRow.Hidden = False
        Rows("67").EntireRow.Hidden = False
    ElseIf Range("E65").Value = "" Then
        Rows("66:67").EntireRow.Hidden = True
    End If
    
    Application.EnableEvents = True

End Sub

注意:我假设您的代码中有更多元素。你写

I'm trying to update the code so that if anyone selects "NA" in cellE58, (there are 3 drop-down options on this cell: Yes/No/NA) itupdates the value of cell E60 and E65 as being NA as well,...

但您的代码不会更新这些单元格。仅隐藏/取消隐藏行或列不会触发 Worksheet_Change事件，因此不会导致无限循环。