Forum Discussion

North_Yorks's avatar
North_Yorks
Copper Contributor
Jun 30, 2020

VBAs in excel for beginners!

Hi,

I have a basic checklist with drop down lists of yes, no or n/a. For certain question if you select no you can skip down to a lower section and miss out some questions but to make the form more user friendly I'd like to hid the rows that aren't required. I have manage to do this for the first question however how do I add multiple commands for further questions? Here's what I have got for the first question:

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E5") = "No" Then
Rows("6:45").EntireRow.Hidden = True
Else
Rows("6:45").EntireRow.Hidden = False
End If
End Sub

 

I copied this command from a tutorial and changed the values but don't fully understand how the function works!

 

Many thanks

Macros and VBA

10 Replies

    • North_Yorks's avatar
      North_Yorks
      Copper Contributor
      Jul 01, 2020

      Riny_van_Eekelen 

      Hi, thanks I've added them all but the first rule is not working it only hides 3 rows to the next rule when it should hide all questions to line 45 - all other rules are working correctly.

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Range("E5") = "No" Then
      Rows("6:45").EntireRow.Hidden = True
      Else
      Rows("6:45").EntireRow.Hidden = False
      End If
      If Range("E7") = "No" Then
      Rows("8:18").EntireRow.Hidden = True
      Else
      Rows("8:18").EntireRow.Hidden = False
      End If
      If Range("E20") = "No" Then
      Rows("21:24").EntireRow.Hidden = True
      Else
      Rows("21:24").EntireRow.Hidden = False
      End If
      If Range("E26") = "No" Then
      Rows("27:36").EntireRow.Hidden = True
      Else
      Rows("27:36").EntireRow.Hidden = False
      End If
      If Range("E38") = "No" Then
      Rows("39:45").EntireRow.Hidden = True
      Else
      Rows("39:45").EntireRow.Hidden = False
      End If
      End Sub

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jul 01, 2020

        North_Yorks Insert "Exit Sub" before the first "Else". Then it works. When E5 = No, you want to hide all. No need to check any further. Without the exit code, the macro check the next condition. It is not No, thus it unhides the rows that were hidden in the first step and so on.

        Private Sub Worksheet_Change(ByVal Target As Range)

If Range("E5") = "No" Then
Rows("6:45").EntireRow.Hidden = True
Exit Sub
Else
Rows("6:45").EntireRow.Hidden = False
End If

If Range("E7") = "No" Then
Rows("8:18").EntireRow.Hidden = True
Else
Rows("8:18").EntireRow.Hidden = False
End If

If Range("E20") = "No" Then
Rows("21:24").EntireRow.Hidden = True
Else
Rows("21:24").EntireRow.Hidden = False
End If

If Range("E26") = "No" Then
Rows("27:36").EntireRow.Hidden = True
Else
Rows("27:36").EntireRow.Hidden = False
End If

If Range("E38") = "No" Then
Rows("39:45").EntireRow.Hidden = True
Else
Rows("39:45").EntireRow.Hidden = False
End If

End Sub

         

Resources