VBAs in excel for beginners!

Copper Contributor

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

10 Replies

@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.

@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

@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

 

@Riny_van_Eekelen  Thank you it now works perfectly!

@North_Yorks Great! Glad I could help solving your problem.

@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

@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

 

@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

@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>

 

@Riny_van_Eekelen looks like its working thank you very much :)