Forum Discussion

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

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

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