excel - 检测到不明确的名称 - 轻松修复

Question

我已经阅读了此处存在的类似线程，但对于如何将这些答案转换为我的场景仍然有些困惑。我了解我不能在一张纸上进行 2 次更改。所以，我需要结合这两个条件。

基本上，在“表单”上，我试图根据 2 个单独的输入隐藏两个不同工作表上的行。

对于引用的两张纸，第一条语句运行良好 ($B$11)。

我需要引用表单上的另一个单元格来隐藏正在工作的代码中相同两张表上的其他单元格。我尝试合并，但在尝试合并时继续出错。

帮助将两者结合将不胜感激!

Private Sub Worksheet_Change(ByVal Target As Range)

 Application.ScreenUpdating = False

 If Intersect(Target, Range("$B$11")) Is Nothing Then Exit Sub

 If Target.Address = ("$B$11") And Target.Value = "Yes" Then
 Sheets("New HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = False
 Sheets("Existing HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = False
 ElseIf Target.Address = ("$B$11") And Target = "No" Then
 Sheets("New HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = True
 Sheets("Existing HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = True

 End If

 Application.ScreenUpdating = True

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

 Application.ScreenUpdating = False

 If Intersect(Target, Range("$E$8")) Is Nothing Then Exit Sub

 If Target.Address = ("$E$8") And Target.Value = "Yes" Then
 Sheets("New HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = False
 Sheets("Existing HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = False
 ElseIf Target.Address = ("$E$8") And Target = "No" Then
 Sheets("New HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = True
 Sheets("Existing HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = True

 End If

 Application.ScreenUpdating = True

End Sub

Answer 1

尝试这个。您可以检查这两个单元格中的任何一个是否已更改，然后根据值执行必要的操作。

如果您可能要更改多个单元格，则需要通过 Target 合并一个循环。 .

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub 'don't do anything if multiple cells changed (could omit)

Application.ScreenUpdating = False

If Target.Address = "$B$11" Then
    If Target.Value = "Yes" Then
        Sheets("New HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = False
        Sheets("Existing HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = False
    ElseIf Target.Value = "No" Then
        Sheets("New HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = True
        Sheets("Existing HSE Start-Up Checklist").Rows("36:42").EntireRow.Hidden = True
    End If
ElseIf Target.Address = "$E$8" Then
    If Target.Value = "Yes" Then
        Sheets("New HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = False
        Sheets("Existing HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = False
    ElseIf Target.Value = "No" Then
        Sheets("New HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = True
        Sheets("Existing HSE Start-Up Checklist").Rows("43:47").EntireRow.Hidden = True
    End If
End If

Application.ScreenUpdating = True

End Sub