Hide Rows IF Macro or Formula

[a2a]

Hi,

I'm looking to design a form within Excel for end-users that essentially acts as a checklist.

I'd like rows to hide/unhide based on "yes/no" values e.g. in cell A3, if you select "no" then rows 5-10 will hide. This would be the principle for the rest of the form...

I've searched our forums and found this thread, however, cannot seem to manipulate the code for rows.

Could you kindly help please?

Many thanks,
Chris

 

[villy]

Simple one,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Selection.Text = "no" Then
    Rows(5).Hidden = True
    Rows(6).Hidden = True
    Rows(7).Hidden = True
    Rows(8).Hidden = True
    Rows(9).Hidden = True
ElseIf Selection.Text = "yes" Then
    Rows(5).Hidden = False
    Rows(6).Hidden = False
    Rows(7).Hidden = False
    Rows(8).Hidden = False
    Rows(9).Hidden = False
End If
Application.ScreenUpdating = True
End Sub

 

[a2a]

Thanks very much...

Forgive me, however, where does it define the cell to determine the value of yes and no?

 

[villy]

Selection.text means anywhere in your activeworksheet as long as you click on a cell with a text no then it will execute.

This is just a simple example how you would hide rows but if you want to use it in your code then show what you've got and let us study how to impart this code.

 

[a2a]

Villly, although you see that as simple code, it's a lot smoother than mine thus I've scrapped what I had and would like to go with yours

I'd like it so that I can use exact cell references rather than "selection.text" since I will hopefully be using a few instances throughout the form. Also, when changing states between "yes and no" the hiding of the rows is not instantaneous? How could this be rectified please?

Many thanks!

 

[a2a]

Just for info:

I was using Control Buttons and named ranges:

Private Sub cmdCollapseAll_Click()
Call setAllPanes(Me, False)
End Sub
Private Sub cmdExpandAll_Click()
Call setAllPanes(Me, True)
End Sub
Private Sub cmdPane1_Click()
Dim rng As Range

Set rng = Me.Range("rng.Toggle1")
Call DetailViewControl(Me, rng)
Set rng = Nothing
End Sub
Private Sub cmdPane2_Click()
Dim rng As Range

Set rng = Me.Range("rng.Toggle2")
Call DetailViewControl(Me, rng)
Set rng = Nothing