multiple hide rows commands on single worksheet

[Legacy 219053]

Hi all,

I'm a novice at Excel and VBA, but have found myself in a position where I need to execute a specific task using VBA and I would like to request some help.

I am creating a userform in a standard question and answer format. Some of the answers are dropdown menus that I created using Data Validation. In an effort to make the worksheet as simple as possible I would like to hide and unhide rows based on the answers in these dropdowns. My problem is that I have multiple places where I would like this to happen (around 15).

In the past, I have hidden rows based on cell values using:

Private Sub Worksheet_Change(ByVal Target As Range)

However, I am under the assumption that one can only use one Worksheet Change Event per worksheet. If that is true, how else could I approach this issue?

 

[taurean]

Hi,

You are right about one Worksheet Change event per worksheet! But it can be used flexibly to cover the range of cells where you'd want to trigger it.

Private Sub Worksheet_Change(ByVal Target As Range)

Blue marked part is the one which lets you refer the Range in the corresponding worksheet. So supposing you are dealing with Range("A1:A15") i.e. you want the code to execute when the Range is A1:A15 then you can use intersect method like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("A1:A15")) Is Nothing Then
'Hiding Row Code
End If
End Sub

 

[Legacy 219053]

Thanks for the reply taurean!

In this worksheet, hiding rows is dependent upon the value in one cell. Although I have multiple cells which I wish to assign "hide rows" code to, here is one.
e.g. If a1=1 then hide rows 3:10, if a1=2 then hide rows 4:10, etc.

So is the target range in this case just ("A1")?

 

[taurean]

Yes, we are moving in right direction. Lightly tested:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    
    Rows("3:10").Hidden = False 'Unhide Rows first before hiding
    
    Select Case Target.Value    'This will be the case to test
    
    Case 1                      'This will work when A1 value = 1
    Rows("3:10").Hidden = True
    
    Case 2                      'This will work when A1 value = 2
    Rows("4:10").Hidden = True
    
    Case Else                   'Unhide Rows if the case doesn't
    Rows("3:10").Hidden = False 'belong the cases specified above
    End Select

End If
End Sub

 

[Legacy 219053]

Thanks taurean! I really appreciate the time you took to look into my question.

That code worked perfectly when it is the only Worksheet Change event in the worksheet. Here is what I used for this specific application:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$60" Then
    
    Rows("61:324").Hidden = False 'Unhide Rows first before hiding
    
    Select Case Target.Value    'This will be the case to test
    
    Case Is = 0                   'This will work when c60 value = 0
    Rows("61:324").Hidden = True
    
    Case Is = 1                      'This will work when c60 value = 1
    Rows("83:324").Hidden = True
    
    Case Is = 2
    Rows("105:324").Hidden = True
    
    Case Is = 3
    Rows("127:324").Hidden = True
    
    Case Is = 4
    Rows("149:324").Hidden = True
    
    Case Is = 5
    Rows("171:324").Hidden = True
    
    Case Is = 6
    Rows("193:324").Hidden = True
    
    Case Is = 7
    Rows("215:324").Hidden = True
    
    Case Is = 8
    Rows("237:324").Hidden = True
    
    Case Is = 9
    Rows("259:324").Hidden = True
    
    Case Is = 10
    Rows("281:324").Hidden = True
    
    Case Is = 11
    Rows("303:324").Hidden = True
    
    Case Is = 12
    Rows("61:324").Hidden = False
    
    Case Else                      'Hide Rows if the case doesn't
    Rows("61:324").Hidden = True  'belong to the cases specified above
    End Select
End If
End Sub

Unfortunately, I have another Worksheet Change Event in the same Worksheet and only one of them will work. If both codes are in the worksheet then I get a "Compile Error: Ambiguous Name Detected: Worksheet_Change" error. Here is that code (which does the same as what you gave me, just with different syntax):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$37" Then
            Range("C38,C39").EntireRow.Hidden = False
        If Target = "Yes" Then
            Range("C38,C39").EntireRow.Hidden = True
            Range("C40:C50").EntireRow.Hidden = False
        ElseIf Target = "No" Then
            Range("C40:C50").EntireRow.Hidden = True
            Range("C38,C39").EntireRow.Hidden = False
        ElseIf Target = "--Select Y / N--" Then
            Range("C38:C50").EntireRow.Hidden = True
        End If
    End If
End Sub

What can I do to get around this? Specifying which cell is the target does not help. I would also like to have more code that would hide rows within rows 61:324. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$61" Then
    
    Rows("62:77").Hidden = False
    
    Select Case Target.Value
    
    Case Is = 0
    Rows("63:77").Hidden = True
    
    Case Is = 1
    Rows("66:77").Hidden = True
    
    Case Is = 2
    Rows("69:77").Hidden = True
    
    Case Is = 3
    Rows("72:77").Hidden = True
    
    Case Is = 4
    Rows("75:77").Hidden = True
     
    Case Is = 5
    Rows("63:77").Hidden = False
    
    Case Else
    Rows("62:77").Hidden = True
    End Select
    
End If
End Sub

Plus, I will be adding more code like the one above.

How do I get around the problem of having more than one Worksheet Change Event on one worksheet?

 

[Legacy 219053]

Oops. I see my problem. I did not correctly follow your instructions. Let me retry before any replies.

 

[Legacy 219053]

Okay, so I added the "If Not Intersect" part to each of my Subs and am still getting the "Ambiguous Name Error" when I change the target cell in each of my Subs. What am I doing wrong? Surely Excel users have the need to have multiple "hide rows" macros within a single worksheet. Is there a better way to go about this? Here is my code for the entire worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$C$37")) Is Nothing Then
    If Target.Address = "$C$37" Then
            Range("C38,C39").EntireRow.Hidden = False
        If Target = "Yes" Then
            Range("C38,C39").EntireRow.Hidden = True
            Range("C40:C50").EntireRow.Hidden = False
        ElseIf Target = "No" Then
            Range("C40:C50").EntireRow.Hidden = True
            Range("C38,C39").EntireRow.Hidden = False
        ElseIf Target = "--Select Y / N--" Then
            Range("C38:C50").EntireRow.Hidden = True
        End If
    End If
End If
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$C$60")) Is Nothing Then
    If Target.Address = "$C$60" Then
    
    Rows("61:324").Hidden = False 'Unhide Rows first before hiding
    
    Select Case Target.Value    'This will be the case to test
    
    Case Is = 0                   'This will work when c60 value = 0
    Rows("61:324").Hidden = True
    
    Case Is = 1                      'This will work when c60 value = 1
    Rows("83:324").Hidden = True
    
    Case Is = 2
    Rows("105:324").Hidden = True
    
    Case Is = 3
    Rows("127:324").Hidden = True
    
    Case Is = 4
    Rows("149:324").Hidden = True
    
    Case Is = 5
    Rows("171:324").Hidden = True
    
    Case Is = 6
    Rows("193:324").Hidden = True
    
    Case Is = 7
    Rows("215:324").Hidden = True
    
    Case Is = 8
    Rows("237:324").Hidden = True
    
    Case Is = 9
    Rows("259:324").Hidden = True
    
    Case Is = 10
    Rows("281:324").Hidden = True
    
    Case Is = 11
    Rows("303:324").Hidden = True
    
    Case Is = 12
    Rows("61:324").Hidden = False
    
    Case Else                      'Hide Rows if the case doesn't
    Rows("61:324").Hidden = True  'belong to the cases specified above
    End Select
End If
End If
End Sub





Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$C$61")) Is Nothing Then
    If Target.Address = "$C$61" Then
    
    Rows("62:77").Hidden = False
    
    Select Case Target.Value
    
    Case Is = 0
    Rows("63:77").Hidden = True
    
    Case Is = 1
    Rows("66:77").Hidden = True
    
    Case Is = 2
    Rows("69:77").Hidden = True
    
    Case Is = 3
    Rows("72:77").Hidden = True
    
    Case Is = 4
    Rows("75:77").Hidden = True
     
    Case Is = 5
    Rows("63:77").Hidden = False
    
    Case Else
    Rows("62:77").Hidden = True
    End Select
End If
End If
End Sub

 

[taurean]

Hi,

I see you can write most of the code which is heartening. In this case, since we are dealing with three specific ranges we need to handle it using following logic like below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$37" Then
    'Write Code for Range("C37") here.
ElseIf Target.Address = "$C$60" Then
    'Write Code for Range("C60") here.
ElseIf Target.Address = "$C$61" Then
    'Write Code for Range("C61") here.
End If
End Sub

I guess this hint help shall work you out the solution.

 

[Legacy 219053]

Of course! Even though I used the "Intersect" term I still had multiple "Private Sub Worksheet_Change" events. Thanks for your help taurean, I'll be sure to pass it on.