Hide or unhide sheets based on cell
 

Would someone be able to provide a suggestion as to how to hide and / or
unhide a group of sheets based on a cell content?
I have a cell that selects a model from a drop down list; I would like to
hide all the sheets not related to this value.
When the value changes, I would like the corresponding sheets to be
un-hidden, and the non-related sheets to go away.
There will be certain sheets that will be visible at all times.

Is this possible?

Thanks

Hide or unhide sheets based on cell
 

This basic example will give you a start

You can copy this event in the sheet module of Sheet1 for example

If you enter 1 in A1 it hide sheet2 and sheet3
If A1 = 2 it unhide them

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
For Each sh In Sheets(Array("Sheet2", "Sheet3"))
If Target.Value = 1 Then sh.Visible = xlSheetHidden
If Target.Value = 2 Then sh.Visible = xlSheetVisible
Next sh
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"billinr" wrote in message ...
Would someone be able to provide a suggestion as to how to hide and / or
unhide a group of sheets based on a cell content?
I have a cell that selects a model from a drop down list; I would like to
hide all the sheets not related to this value.
When the value changes, I would like the corresponding sheets to be
un-hidden, and the non-related sheets to go away.
There will be certain sheets that will be visible at all times.

Is this possible?

Thanks

Hide or unhide sheets based on cell
 

Thanks, Ron.
This is what I needed.


Regards

"Ron de Bruin" wrote:

This basic example will give you a start

You can copy this event in the sheet module of Sheet1 for example

If you enter 1 in A1 it hide sheet2 and sheet3
If A1 = 2 it unhide them

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
For Each sh In Sheets(Array("Sheet2", "Sheet3"))
If Target.Value = 1 Then sh.Visible = xlSheetHidden
If Target.Value = 2 Then sh.Visible = xlSheetVisible
Next sh
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"billinr" wrote in message ...
Would someone be able to provide a suggestion as to how to hide and / or
unhide a group of sheets based on a cell content?
I have a cell that selects a model from a drop down list; I would like to
hide all the sheets not related to this value.
When the value changes, I would like the corresponding sheets to be
un-hidden, and the non-related sheets to go away.
There will be certain sheets that will be visible at all times.

Is this possible?

Thanks