Jun 30 2020 01:27 AM
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
Jun 30 2020 03:00 AM - edited Jun 30 2020 03:00 AM
Just repeat the code from If to End if , though with a different cell to check and different rows to hide or unhide.
Jul 01 2020 07:25 AM
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
Jul 01 2020 07:59 AM
@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
Jul 01 2020 08:22 AM
@Riny_van_Eekelen Thank you it now works perfectly!
Jul 01 2020 08:38 AM
@North_Yorks Great! Glad I could help solving your problem.
Jul 06 2020 03:48 AM
@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
Jul 06 2020 04:12 AM
@North_Yorks You could paste the following code into the code window for the "Thisworkbook" tab in the VB editor, similar to how you pasted the other code into the Sheet1 code window. See if you can get it to work in your real workbook. It worked on my end.
Private Sub Workbook_Open()
Rows("8:18").EntireRow.Hidden = True
Rows("21:24").EntireRow.Hidden = True
Rows("27:36").EntireRow.Hidden = True
Rows("39:45").EntireRow.Hidden = True
End Sub
Jul 13 2020 01:37 AM
@Riny_van_Eekelen Hi, this worked great for my first worksheet but I have 2 more and they have a different amount of questions so different amount of rows need to be collapsed. Would I just copy the said command but specify which worksheet and which lines? How would I specify which worksheet?
Many thanks
Jul 13 2020 02:26 AM
@North_Yorks See attached file with my original code in it. Three sheets, all with the same set-up, just for testing purposes. I believe it works as you want it.
In the code for "Thisworkbook", add a line that activates each sheet before you specify which rows to hide in it upon opening the workbook, similar to this:
Sheets("Sheet1").Activate
<code for hiding rows>
Sheets("Sheet2").Activate
<code for hiding rows>
Sheets("Sheet3").Activate
<code for hiding rows>
Jul 13 2020 03:06 AM
@Riny_van_Eekelen looks like its working thank you very much :)