Creating Mutliple Worksheet_Change Event Procedures

[Sundae]

Hi there, I am finding so difficult to start a new thread. My question is similar to the above. I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a15").Value = "Exempt" And Range("F6").Value = "Exempt" Then
Rows("19").EntireRow.Hidden = True
Rows("21").EntireRow.Hidden = True
ElseIf Range("a15").Value = "Exempt" And Range("F6").Value = "Non-exempt" Then
Rows("19").EntireRow.Hidden = False
Rows("21").EntireRow.Hidden = False
ElseIf Range("a15").Value = "Non-exempt" And Range("F6").Value = "Exempt" Then
Rows("19").EntireRow.Hidden = False
Rows("21").EntireRow.Hidden = False
End If
End Sub

I need to create a second change action, but I get ambiguous name detected vba error, where do I change my code name? Thank you!


Split from: https://www.mrexcel.com/forum/excel-questions/1068094-hiding-rows-vba-question.html

 

[Joe4]

OK, I think I may see what you are getting at. Let's look at a possible example.

Let's say that A15 is "Exempt" and F6 is "Non-exempt".
And here is your code, formatted nicely.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("a15").Value = "Exempt" And Range("F6").Value = "Exempt" Then
        rows("19").EntireRow.Hidden = True
        rows("21").EntireRow.Hidden = True
    ElseIf Range("a15").Value = "Exempt" And Range("F6").Value = "Non-exempt" Then
[COLOR=#ff0000]        rows("19").EntireRow.Hidden = False
        rows("21").EntireRow.Hidden = False[/COLOR]
    ElseIf Range("a15").Value = "Non-exempt" And Range("F6").Value = "Exempt" Then
        rows("19").EntireRow.Hidden = False
        rows("21").EntireRow.Hidden = False
    ElseIf Range("a15").Value = "Exempt" Then
[COLOR=#ff0000]        rows("36:37").EntireRow.Hidden = True[/COLOR]
    ElseIf Range("a15").Value = "Non-exempt" Then
        rows("41:42").EntireRow.Hidden = True
    End If

End Sub

Are you saying that you want BOTH blocks in red to run?

If so, then you want to remove the last two things you added from the big, nested IF statement you added, and make them their own block.
Because in a large, nested IF ELSEIF statement, once it meets one condition, it stops there and does not evaluate the rest of your block.

So I think this may be what you are looking for:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Block 1
    If Range("a15").Value = "Exempt" And Range("F6").Value = "Exempt" Then
        rows("19").EntireRow.Hidden = True
        rows("21").EntireRow.Hidden = True
    ElseIf Range("a15").Value = "Exempt" And Range("F6").Value = "Non-exempt" Then
        rows("19").EntireRow.Hidden = False
        rows("21").EntireRow.Hidden = False
    ElseIf Range("a15").Value = "Non-exempt" And Range("F6").Value = "Exempt" Then
        rows("19").EntireRow.Hidden = False
        rows("21").EntireRow.Hidden = False
    End If
    
'   Block 2
    If Range("a15").Value = "Exempt" Then
        rows("36:37").EntireRow.Hidden = True
    ElseIf Range("a15").Value = "Non-exempt" Then
        rows("41:42").EntireRow.Hidden = True
    End If

End Sub

 

[Sundae]

You are AWESOME!!!! Thank you so much! I am so grateful for this forum and the assistance from experts like you provide.

Happy New Year!!

 

[Joe4]

You are welcome.

Glad I was able to help.