Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide/unhide | Excel Discussion (Misc queries) | |||
Hide sheets based on Cell value | Excel Discussion (Misc queries) | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) | |||
Hide Unhide | Excel Discussion (Misc queries) | |||
Is there a way to HIDE a row based on a value of a cell ? | Excel Discussion (Misc queries) |