Hide Unhide Rows

[emilemil22]

Helloe everyone,

I am completely new to VBA so your help will be appreciated.

I want automatically hide/unhide rows based on cell value. The cell value could have only 2 options: YES or NO

So whenever cell value= YES then hide rows 2:10 and unhide 10:15
whenever cell value= NO then unhide rows 2:10 and hide 10:15

Thanks for your help!

 

[Mentor82]

Hi,
I need you provide more information. What do you mean by whenever? What is the range the algorism should chech the yes/no value?

 

[emilemil22]

Thanks for the reply.

Basically I am making a form. User is asked 'Do you have a second citizneship?" If he types in Yes in the cell then multiple rows appear asking him to fill in country, passport number etc.. When the user types in No or leaves it blank then no further questions (rows) appear.

Was I able to explain it?

 

[Mentor82]

User tyoes yes or no in one cell is that right?

 

[emilemil22]

So whenever user types YES then hide rows 2:10 and unhide 10:15
So whenever user types NO then unhide rows 2:10 and hide 10:15

 

[Mentor82]

You do not get me I suppose.
In order to hide/unhide rows 2:10,10:15 I need to know which cell or range of cells I need to check if user types in yes or no value but you have not provided me with such information yet.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "[COLOR=#ff0000]A1[/COLOR]" Then
      Rows("2:10").Hidden = LCase(Target) = "yes"
      Rows("10:15").Hidden = LCase(Target) <> "yes"
   End If
End Sub

Change the value in red to the cell with the Yes/No answer.
This needs to go in the relevant sheet module

 

[emilemil22]

Hi,

The curren active sheet where the rows are to be hidden is Sheet1
The value of YES or NO is types in Cell A2 in different sheet calles Sheet 2
Is this what you needed?

Thankyou very much

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A2" Then
      Sheets("Sheet1").Rows("2:10").Hidden = LCase(Target) = "yes"
      Sheets("Sheet1").Rows("10:15").Hidden = LCase(Target) <> "yes"
   End If
End Sub

This needs to go in the sheet module for sheet2

 

[Mentor82]

Hi,
Exactly, this is what I was asking about. Here you have the code. Paste it worksheets 2 (not in the module).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws as worksheet
Set ws = worksheets(2)
If Not Intersect(Target, Range("A2")) Is Nothing Then
   ws.Rows("2:10").Hidden = LCase(Target) = "yes"  
 ws.Rows("10:15").Hidden = LCase(Target) <> "yes"

End if
Set ws= nothing
End sub