unhide rows based on data validation

[cathy]

I have a data validation list in cell $E$30 of either "0" or "1".

If the user selects "0" I want to hide rows 144 - 238.
If the user selects "1" I want rows 144-238 to appear.

I tried the following code - but it didn't seem to work. Any help is
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Range("E30").Value = "0" Then
Rows("144:238").EntireRow.Hidden = True
ElseIf Range("E30").Value = "1" Then
Rows("144:238").EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub

Rick

 

[Per Jessen]

I have a data validation list in cell $E$30 of either "0" or "1".

If the user selects "0" I want to hide rows 144 - 238.
If the user selects "1" I want rows 144-238 to appear.

I tried the following code - but it didn't seem to work.  Any help is
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Hi Cathy

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("E30"))
If Not isect Is Nothing Then
If Target.Value = 0 Then
Range("A144:A238").EntireRow.Hidden = True
Else
Range("A144:A238").EntireRow.Hidden = False
End If
End If
End Sub

Regards,
Per

 

[Rick Rothstein \(MVP - VB\)]

Actually, it looks like the EntireRow qualifier is not required with a call
to Rows... this seems to work fine...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Range("E30").Value = "0" Then
Rows("144:238").Hidden = True
ElseIf Range("E30").Value = "1" Then
Rows("144:238").Hidden = False
End If
Application.ScreenUpdating = True
End Sub

Rick

 

[cathy]

PERFECT!! Thank you VERY much!!!
--
Cathy

Actually, it looks like the EntireRow qualifier is not required with a call
to Rows... this seems to work fine...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Range("E30").Value = "0" Then
Rows("144:238").Hidden = True
ElseIf Range("E30").Value = "1" Then
Rows("144:238").Hidden = False
End If
Application.ScreenUpdating = True
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, Per include a filter which I inadvertently left out. Combining
such a filter into the code I previously posted yields this code which you
should use instead of what I posted earlier...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$30" Then
Application.ScreenUpdating = False
If Range("E30").Value = "0" Then
Rows("144:238").Hidden = True
ElseIf Range("E30").Value = "1" Then
Rows("144:238").Hidden = False
End If
Application.ScreenUpdating = True
End If
End Sub

The filter on the Target (the cell you are changing) stops the code from
executing when something else other than E30 is changed. By the way, what
did you want to have happen if E30 is set to something other than 0 or 1? Or
are those possible values enforced in code somehow? Right now, if you set
E30 to something else, nothing happens to affect the visibility of those
rows.

Rick

 

[cathy]

Now, I'm running into another problem. This worksheet is password protected.
When I password protect the VBA code will not work - is there any way to
work around this?

 

[Rick Rothstein \(MVP - VB\)]

Look up the Protect and Unprotect methods in the help files (type Protect
into the Immediate window and hit F1; See Also on its help page can get you
to the help page for Unprotect).

By the way, make sure you see my newest posting against the suggested code
you indicated you were going to use... I made a modification to it.

Rick