CODEWITHSUNDEEP
excelvbaambiguous
brettd03
brettd03

Reputation: 11

Ambiguous Name Detected- Easy Fix

I've read through the similar threads that exist here and am still a bit confused on how to translate those answers to my scenario. I understand that I cannot have 2 changes in one sheet. So, I need to combine the two conditions.

Basically, on a "form", I'm trying to hide rows on two different sheets based on 2 separate inputs.

First statement is working perfectly ($B$11)for the two sheets referenced.

There is another cell on the form that I need to reference to hide additional cells on the same two sheets in the code that is working. I tried to combine but continue to get errors on my attempts to combine.

Help combining the two would be much appreciated!

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

Upvotes: 1

Views: 297

Answers (1)

SJR
SJR

Reputation: 23081

Try this. You can just check if either of those two cells have changed and then do the necessary depending on the value.

If you might be changing more than one cell you'd need to incorporate a loop through 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

Upvotes: 2

Related Questions