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

North_Yorks
Copper Contributor
Jul 01, 2020
Riny_van_Eekelen Thank you it now works perfectly!
- Riny_van_Eekelen
  Platinum Contributor
  Jul 01, 2020
  North_Yorks Great! Glad I could help solving your problem.
  - North_Yorks
    Copper Contributor
    Jul 06, 2020
    Riny_van_Eekelen spreadsheet it still working perfectly but there is a 'nice-ity' we'd like if it is at all possible to do!
    Is it possible to have it, so when you open the document all the questions are collapsed - as if you have selected no for each, but without having 'no' typed/selected in them? But still have the options to select no yes n/a in the drop down lists I already have?
    As I said its not a necessity but a nice to have!
    
    Many thanks in advance