☰

如何根据两个单独的下拉框隐藏不同的行？

tag-icon • tag-icon microsoft-excel vba
如何根据两个单独的下拉框隐藏不同的行？

我确信有一个简单的解决方法，但我不明白为什么第一个 if 语句有效而第二个无效。第一个隐藏 57:68 的语句有效，但第二个隐藏 70:78 的语句无效。VBA 没有给我错误。有人能帮我解决这个问题吗？谢谢！

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4")) Is Nothing Or Target.Cells.Count > 1 Then
    Exit Sub

ElseIf Range("B4").Value = "NO" Then
    Rows("57:68").EntireRow.Hidden = True

ElseIf Range("B4").Value = "YES" Then
    Rows("57:68").EntireRow.Hidden = False

End If

If Intersect(Target, Range("B5")) Is Nothing Or Target.Cells.Count > 1 Then
    Exit Sub

ElseIf Range("B5").Value = "NO" Then
    Rows("70:78").EntireRow.Hidden = True

ElseIf Range("B5").Value = "YES" Then
    Rows("70:78").EntireRow.Hidden = False

End If

End Sub

答案1

这应该有效：

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Not Intersect(Target, Range("B4")) Is Nothing And Target.Cells.Count = 1 Then
            If Target = "No" Then Rows("57:68").EntireRow.Hidden = True
            If Target = "Yes" Then Rows("57:68").EntireRow.Hidden = False
End If
If Not Intersect(Target, Range("B5")) Is Nothing And Target.Cells.Count = 1 Then
            If Target = "NO" Then Rows("70:78").EntireRow.Hidden = True
            If Target = "YES" Then Rows("70:78").EntireRow.Hidden = False
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

答案2

除非您正在检查其他条件，否则您的代码可以简化为：

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B4").Value = "NO" Then
    Rows("57:68").EntireRow.Hidden = True

ElseIf Range("B4").Value = "YES" Then
    Rows("57:68").EntireRow.Hidden = False

End If

If Range("B5").Value = "NO" Then
    Rows("70:78").EntireRow.Hidden = True

ElseIf Range("B5").Value = "YES" Then
    Rows("70:78").EntireRow.Hidden = False

End If

End Sub

相关内容