Forum Discussion

CTinson's avatar
CTinson
Copper Contributor
Nov 30, 2021

Macro for hiding/unhiding rows based on multiple drop down menu needed please

Hi I'm really really beginner at this macro/VBA stuff. My expertise goes as far as googling and using formulas on forums etc 

 

I'm trying to produce a dynamic 'drop down flow chart selector' ... thing which will hide and unhide rows to remove or add the next question/drop down selection.

 

It consists of 4 drop downs in total and has drop down selections which will either stop the person using it from proceeding or advance to the next question to continue the process.

 

Drops downs are in Cells C13, C25, C37, C49

 

I wanted to start with only rows 1:21 visible

 

DD1 - 4 codes can be selected

 

Code 1 - If selected hide row 22:58
Code 2 - If selected move to DD2 (C25) unhide rows 22:32
Code 3 - If selected move to DD2 (C25) unhide rows 22:32
Code 4 - If selected hide rows 22:58

 

DD2 - has 'Yes' and 'No' answer

 

Code 2 - Yes - Continue to DD3 (C37) unhide rows 33:44
Code 2 - No - hide rows 33:58
Code 3 - Yes - Continue to DD3 (C37) rows 33:44
Code 3 - No - hide rows 33:58

 

DD3 - has 'Yes' and 'No' answers

 

Code 2 - Yes - Continue to  DD4 (C49) unhide rows 45:58
Code 2 - No - hide rows 45:58
Code 3 - Yes - Continue to DD4 (C49) - unhide rows 45:58
Code 3 - No - hide rows 45:58

 

The following code worked well for the first part (have underlined) but I'm stuck now how to unhide/hide more rows for drop down 2 and so on.

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range

Set Triggercell = Range("C13")

If Not Application.Intersect(Triggercell, Target) Is Nothing Then
If Triggercell.Value = "Code 1" Then
Rows("22:58").Hidden = True
ElseIf Triggercell.Value = "Code 4" Then
Rows("22:58").Hidden = True
ElseIf Triggercell.Value = "Code 2" Then
Rows("22:32").Hidden = False
ElseIf Triggercell.Value = "Code 3" Then
Rows("22:32").Hidden = False

End If
End If
End Sub

 

Any help would be appreciated, thank you

excel
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 30, 2021

    CTinson 

    How about

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Select Case Target.Address
        Case "$C$13"
            Select Case Range("C13").Value
                Case "Code 2", "Code 3"
                    Range("A22:A32").EntireRow.Hidden = False
                    Range("C25").Select
                    Range("C25").ClearContents
                Case "Code 1", "Code 4"
                    Range("A22:A58").EntireRow.Hidden = True
            End Select
        Case "$C$25"
            Select Case Range("C25").Value
                Case "Yes"
                    Range("A33:A44").EntireRow.Hidden = False
                    Range("C37").Select
                    Range("C37").ClearContents
                Case "No"
                    Range("A33:A58").EntireRow.Hidden = True
            End Select
        Case "$C$37"
            Select Case Range("C37").Value
                Case "Yes"
                    Range("A45:A58").EntireRow.Hidden = False
                    Range("C49").Select
                    Range("C49").ClearContents
                Case "No"
                    Range("A45:A58").EntireRow.Hidden = True
            End Select
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Resources