Multiple subs in VBA Code ?

lmjannin

New Member
Joined
Oct 12, 2019
Messages
7
Good morning.

I am working on the same spreadsheet described in this thread (https://www.mrexcel.com/forum/excel-questions/1112241-hiding-rows-using-vba-multiple-criteria.html)

It is a sheet where based on the answers provided in a set of dropdown boxes, certain rows need to be hidden entirely.

Per the thread listed above, I was able to get the first set to show/hide appropriately.

I need to add a second set of rules for a separate section on the workbook to hide/show rows in the same manner.

Using the same syntax as a part of the same sub, the second group of rules isn't working properly.

I am super new to VBA - I am hoping this is something simple that I haven't structured properly.

Please and thank you ahead of time :) I highlighted in red below the part that is not working.

THANK YOU!

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
   
If Range("AA4") = "No" Then
   Rows("163:166").EntireRow.Hidden = True
Else
   Rows("163:166").EntireRow.Hidden = False
End If
    
If Range("AA5") = "No" Then
   Rows("167:168").EntireRow.Hidden = True
Else
   Rows("167:168").EntireRow.Hidden = False
End If


If Range("AA6") = "No" Then
   Rows("169:172").EntireRow.Hidden = True
Else
   Rows("169:172").EntireRow.Hidden = False
End If




If Not Intersect(Target, Range("AA3")) Is Nothing Then
    Me.Rows.Hidden = False
    
Select Case Range("AA3").Value
        Case 1
            Me.Rows("45:161").EntireRow.Hidden = True
        Case 2
            Me.Rows("58:161").EntireRow.Hidden = True
        Case 3
            Me.Rows("71:161").EntireRow.Hidden = True
        Case 4
            Me.Rows("84:161").EntireRow.Hidden = True
        Case 5
            Me.Rows("97:161").EntireRow.Hidden = True
        Case 6
            Me.Rows("110:161").EntireRow.Hidden = True
        Case 7
            Me.Rows("123:161").EntireRow.Hidden = True
        Case 8
            Me.Rows("136:161").EntireRow.Hidden = True
        Case 9
            Me.Rows("149:161").EntireRow.Hidden = True
        Case Else
            Me.Rows.Hidden = False


End Select
End If
End Sub


Private Sub Worksheet2_Change(ByVal Target As Range)






If Not Intersect(Target, Range("AA8")) Is Nothing Then
   Me.Rows.Hidden = False


Select Case Range("AA8").Value
   Case 1
        Me.Rows("190:413").EntireRow.Hidden = True
   Case 2
        Me.Rows("206:413").EntireRow.Hidden = True
   Case 3
        Me.Rows("222:413").EntireRow.Hidden = True
   Case 4
        Me.Rows("238:413").EntireRow.Hidden = True


   Case Else
        Me.Rows.Hidden = False


End Select
End If
End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
You cannot change the name of the procedure as it's an in-built event.
Instead you need to add this
Code:
If Not Intersect(Target, Range("AA8")) Is Nothing Then
   Me.Rows.Hidden = False


Select Case Range("AA8").Value
   Case 1
        Me.Rows("190:413").EntireRow.Hidden = True
   Case 2
        Me.Rows("206:413").EntireRow.Hidden = True
   Case 3
        Me.Rows("222:413").EntireRow.Hidden = True
   Case 4
        Me.Rows("238:413").EntireRow.Hidden = True


   Case Else
        Me.Rows.Hidden = False


End Select
End If
Just before the End Sub line in the first set of code.
 

lmjannin

New Member
Joined
Oct 12, 2019
Messages
7
Thank you! I added the code where indicated, and that part of the code is now working. However when I added it, the first set of code above it stopped working. Now, regardless of what I populate in AA3, I am seeing all of the lines that I don't want to see.

This is the part of the code that is no longer working...that was my reason for trying to separate them. :) Thank you!

If Not Intersect(Target, Range("AA3")) Is Nothing Then
Me.Rows.Hidden = False

Select Case Range("AA3").Value
Case 1
Me.Rows("45:161").EntireRow.Hidden = True
Case 2
Me.Rows("58:161").EntireRow.Hidden = True
Case 3
Me.Rows("71:161").EntireRow.Hidden = True
Case 4
Me.Rows("84:161").EntireRow.Hidden = True
Case 5
Me.Rows("97:161").EntireRow.Hidden = True
Case 6
Me.Rows("110:161").EntireRow.Hidden = True
Case 7
Me.Rows("123:161").EntireRow.Hidden = True
Case 8
Me.Rows("136:161").EntireRow.Hidden = True
Case 9
Me.Rows("149:161").EntireRow.Hidden = True
Case Else
Me.Rows.Hidden = False

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
Try it like
Code:
If Not Intersect(Target, Range("AA3")) Is Nothing Then
    Me.Rows("45:161").Hidden = False
    
    Select Case Range("AA3").Value
            Case 1
                Me.Rows("45:161").EntireRow.Hidden = True
            Case 2
                Me.Rows("58:161").EntireRow.Hidden = True
            Case 3
                Me.Rows("71:161").EntireRow.Hidden = True
            Case 4
                Me.Rows("84:161").EntireRow.Hidden = True
            Case 5
                Me.Rows("97:161").EntireRow.Hidden = True
            Case 6
                Me.Rows("110:161").EntireRow.Hidden = True
            Case 7
                Me.Rows("123:161").EntireRow.Hidden = True
            Case 8
                Me.Rows("136:161").EntireRow.Hidden = True
            Case 9
                Me.Rows("149:161").EntireRow.Hidden = True
    End Select
ElseIf Not Intersect(Target, Range("AA8")) Is Nothing Then
    Me.Rows("190:143").Hidden = False

    Select Case Range("AA8").Value
       Case 1
            Me.Rows("190:413").EntireRow.Hidden = True
       Case 2
            Me.Rows("206:413").EntireRow.Hidden = True
       Case 3
            Me.Rows("222:413").EntireRow.Hidden = True
       Case 4
            Me.Rows("238:413").EntireRow.Hidden = True
    End Select
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,084,853
Messages
5,380,302
Members
401,662
Latest member
oct2019

Some videos you may like

This Week's Hot Topics

Top