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 ...
Macros and VBA
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 30, 2020

North_Yorks 

Just repeat the code from If to End if , though with a different cell to check and different rows to hide or unhide.

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