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

 

[mumps]

To start a new thread, click the "Post New Thread" button at the upper left corner of the Forum window.

 

[Joe4]

I have moved your post to a new thread. I responded to the question you posed about posting with some questions for you, but you have not responded.

Regarding this question, you are not allowed to have multiple procedures by the same name in a single module (hence the "ambiguity" error message - if you call a macro and there are two matching with the same name, it wouldn't know which one to run).

You must put your second block of code within the same module here. Just add another IF... THEN block underneath what you have.

Note with Worksheet_Change macros, it is often common to have them only fire if certain cells are updated, so they don't needlessly run everytime a change is made (if a change is made to a whole different area that has no impact on what you are trying to do). "Target" is the range that was just updated that fires this macro. So you can check the address of target, or see if it falls within a certain range or not, and if it does, then perform your steps.

 

[Sundae]

Thank you!

 

[Joe4]

You are welcome.

If you run into any issues combining the two, feel free to post back to this thread and we can help you out.

 

[Sundae]

Thank you for responding so quickly, so I added the two new blocks, but when I update the cell it references, the rows are not hidden. This is what I have:

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

ElseIf 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


I really appreciate your help!

 

[Joe4]

Are cells A15 and F6 hard-coded values or formulas?
Can you give me an example of one that does not work?
Tell me what cells are you updating to what value, and I will try it on your code here on my end.

 

[Sundae]

Cells A15 and F6 are pick lists, with only Exempt and Non-exempt as options.

These two are not working: notice that these two are only referencing one cell a15

ElseIf Range("a15").Value = "Exempt" Then
Rows("36:37").EntireRow.Hidden = True

ElseIf Range("a15").Value = "Non-exempt" Then
Rows("41:42").EntireRow.Hidden = True


Thanks again

 

[Joe4]

It seems to work fine for me.
Note that the way you have your code written, those will ONLY work if the value in F6 is blank (i.e. F6 is NOT "Exempt" or Non-exempt").
Can you confirm that F6 is blank in those situations?

 

[Sundae]

No, F6 is not blank, as it needs to be populated for the code above to work