Hide/Unhide Rows based on Cell Value

tlc53

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
Hi there,

I have a list of questions which are answered by Yes/No Option buttons. The option buttons are linked to the neighbouring cell and return value 1 if Yes, and 2 if No.

So the user can add a comment, I would like if No is selected (cell value is 2), then the row directly under it is unhidden. If Yes is selected (cell value is 1) or neither option button are selected yet (cell value 0) then the row directly under remains hidden.

I'm a little stumped as to how to set this VBA code out. Here is my not-so-good attempt but hopefully it shows what I am trying to do. The result to the questions go from "I14:I62" so tying to do it this way, I would need to repeat the code many more times still.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I14").Value = 2 Then
    Range("I15").EntireRow.Hidden = False
Else
    Range("I15").EntireRow.Hidden = True

End If

If Range("I16").Value = 2 Then
    Range("I17").EntireRow.Hidden = False
Else
    Range("I17").EntireRow.Hidden = True

End If

If Range("I18").Value = 2 Then
    Range("I19").EntireRow.Hidden = False
Else
    Range("I19").EntireRow.Hidden = True

End Sub

Any help with this would be much appreciated.

Thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
How about something like this (untested).
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I14:I62")) Is Nothing Then
    If Target.Row Mod 2 = 0 Then
        Target.Offset(1).EntireRow.Hidden = Not (Target.Value = 2)
    End If
End If
End Sub
 
Upvote 0
jasonb75 said:
How about something like this (untested).
Click to expand...
Thank you! That looks like the sort of code I'm after. However, for some reason, it isn't working/doing anything :unsure:
And I've just worked out why. It's because the number 0, 1 and 2 are not manually entered, but entered automatically via the option button.
Your code works perfectly if I manually enter the number in I14 for example.
Is there a way for it to work off formula driven/the option button answers??
 
Upvote 0
At a glance it would appear that it doesn't work that way, I thought that it should work as the option button enters a value directly into the cell but have not had cause to try it until now.

Do you have any formulas in your sheet that would be affected by the option button change?

The only way that I have found to force the code to run is by a formula recalculating. The option button alone is not enough to trigger anything that I have tried so far.

A simple formula such as =COUNT(I14:I62) would be enough to trigger the code to run if you have somewhere that you can add that to the sheet.
VBA Code: 
Private Sub Worksheet_calculate()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("I14:I62")
    If c.Row Mod 2 = 0 Then
        c.Offset(1).EntireRow.Hidden = Not (c.Value = 2)
    End If
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
 
Upvote 0
jasonb75 said:
At a glance it would appear that it doesn't work that way, I thought that it should work as the option button enters a value directly into the cell but have not had cause to try it until now.

Do you have any formulas in your sheet that would be affected by the option button change?

The only way that I have found to force the code to run is by a formula recalculating. The option button alone is not enough to trigger anything that I have tried so far.

A simple formula such as =COUNT(I14:I62) would be enough to trigger the code to run if you have somewhere that you can add that to the sheet.
VBA Code: 
[QUOTE="jasonb75, post: 5723408, member: 108602"]
At a glance it would appear that it doesn't work that way, I thought that it should work as the option button enters a value directly into the cell but have not had cause to try it until now.

Do you have any formulas in your sheet that would be affected by the option button change?

The only way that I have found to force the code to run is by a formula recalculating. The option button alone is not enough to trigger anything that I have tried so far.

A simple formula such as =COUNT(I14:I62) would be enough to trigger the code to run if you have somewhere that you can add that to the sheet.
[CODE=vba]
Private Sub Worksheet_calculate()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("I14:I62")
    If c.Row Mod 2 = 0 Then
        c.Offset(1).EntireRow.Hidden = Not (c.Value = 2)
    End If
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Click to expand...

[/QUOTE]
No, I have no other formulas in my sheet which are affected.
That new VBA code and the Count formula did the trick, thank you. It does run and work as I would want it to. The only thing is, it's slow to process. Often when the user is completing the form, they might put Yes for all the questions and so no comments/rows unhidden are needed. As it needs to calculate each question, it would take quite some time to complete.
If it only ran if No (value 2) came up, that could work. Otherwise, I may need to rethink how this form is laid out.
 
Upvote 0
Unfortunately because it needs to run on calculation there is no way to link it to the change of a specific cell, it has to go through all of them each time which is why it is slower.

Does it need to hide the rows after each change or can it all just be done at the end? Maybe a 'submit' button to run the code once the user has finished rather than automating it?

I'm going to be offline for a few hours but will try and check back before the end of the day with updated code if any is required.
 
Upvote 0
jasonb75 said:
Unfortunately because it needs to run on calculation there is no way to link it to the change of a specific cell, it has to go through all of them each time which is why it is slower.

Does it need to hide the rows after each change or can it all just be done at the end? Maybe a 'submit' button to run the code once the user has finished rather than automating it?

I'm going to be offline for a few hours but will try and check back before the end of the day with updated code if any is required.
Click to expand...
Unfortunately, the rows need to be unhidden as each question is answered, so they can add a comment/note regarding.
It needs to function quickly, so I thought I'd try a Drop Down List instead. It all works okay however, I would prefer for it to be faster. There is still a lag when each question (yes/no) is answered. Is there a better way to be doing this that you can think of?
Here's my current VBA code. Not very sophisticated but it functions :)

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("G14") = "No" Then
        Range("15:15").EntireRow.Hidden = False
    Else
        Range("15:15").EntireRow.Hidden = True
    End If

    If Range("G16") = "No" Then
        Range("17:17").EntireRow.Hidden = False
    Else
        Range("17:17").EntireRow.Hidden = True
    End If
    
    If Range("G18") = "No" Then
        Range("19:19").EntireRow.Hidden = False
    Else
        Range("19:19").EntireRow.Hidden = True
    End If
    
    If Range("G21") = "No" Then
        Range("22:22").EntireRow.Hidden = False
    Else
        Range("22:22").EntireRow.Hidden = True
    End If
        
    If Range("G23") = "No" Then
        Range("24:24").EntireRow.Hidden = False
    Else
        Range("24:24").EntireRow.Hidden = True
    End If
            
    If Range("G25") = "No" Then
        Range("26:26").EntireRow.Hidden = False
    Else
        Range("26:26").EntireRow.Hidden = True
    End If
        
    If Range("G27") = "No" Then
        Range("28:28").EntireRow.Hidden = False
    Else
        Range("28:28").EntireRow.Hidden = True
    End If
        
    If Range("G29") = "No" Then
        Range("30:30").EntireRow.Hidden = False
    Else
        Range("30:30").EntireRow.Hidden = True
    End If
        
    If Range("G31") = "No" Then
        Range("32:32").EntireRow.Hidden = False
    Else
        Range("32:32").EntireRow.Hidden = True
    End If
        
    If Range("G33") = "No" Then
        Range("34:34").EntireRow.Hidden = False
    Else
        Range("34:34").EntireRow.Hidden = True
    End If
        
    If Range("G35") = "No" Then
        Range("36:36").EntireRow.Hidden = False
    Else
        Range("36:36").EntireRow.Hidden = True
    End If
       
    If Range("G41") = "No" Then
        Range("42:42").EntireRow.Hidden = False
    Else
        Range("42:42").EntireRow.Hidden = True
    End If
        
    If Range("G43") = "No" Then
        Range("44:44").EntireRow.Hidden = False
    Else
        Range("44:44").EntireRow.Hidden = True
    End If
        
    If Range("G45") = "No" Then
        Range("46:46").EntireRow.Hidden = False
    Else
        Range("46:46").EntireRow.Hidden = True
    End If
        
    If Range("G47") = "No" Then
        Range("48:48").EntireRow.Hidden = False
    Else
        Range("48:48").EntireRow.Hidden = True
    End If
        
    If Range("G49") = "No" Then
        Range("50:50").EntireRow.Hidden = False
    Else
        Range("50:50").EntireRow.Hidden = True
    End If
        
    If Range("G51") = "No" Then
        Range("52:52").EntireRow.Hidden = False
    Else
        Range("52:52").EntireRow.Hidden = True
    End If
        
    If Range("G54") = "No" Then
        Range("55:55").EntireRow.Hidden = False
    Else
        Range("55:55").EntireRow.Hidden = True
    End If
        
    If Range("G56") = "No" Then
        Range("57:57").EntireRow.Hidden = False
    Else
        Range("57:57").EntireRow.Hidden = True
    End If
        
    If Range("G58") = "No" Then
        Range("59:59").EntireRow.Hidden = False
    Else
        Range("59:59").EntireRow.Hidden = True
    End If
        
    If Range("G60") = "No" Then
        Range("61:61").EntireRow.Hidden = False
    Else
        Range("61:61").EntireRow.Hidden = True
    End If
        
    If Range("G62") = "No" Then
        Range("63:63").EntireRow.Hidden = False
    Else
        Range("63:63").EntireRow.Hidden = True
    End If


End Sub
 
Upvote 0
@tlc53, a minor tweak to @jasonb75's post #2 code might look like this, and is likely to run faster than the code of your previous post.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Intersect(Target, Range("G14:G62")) Is Nothing Then
        For Each c In Target
            If c.Row Mod 2 = 0 Then
                c.Offset(1).EntireRow.Hidden = Not (StrComp(c.Value, "No", vbTextCompare) = 0)
            End If
        Next c
    End If
End Sub
 
Upvote 0
Solution
You are welcome, glad we could help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
412
kevin9999
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
931
dmt32
D
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
402
kevin9999
K
N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
131
bobsan42
bobsan42
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
359
NoSparks
N

Forum statistics

Threads
1,214,798
Messages
6,121,636
Members
449,043
Latest member
farhansadik

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top