Hide a sheet with a variable name   

G'Day

I am trying to hide a sheet(s) based on a cell value. I have found an old 
post thats uses:

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

and this works perfectly. EXCEPT... my problem is that my sheet names change 
(in this case Sheet2) depending on the value of other cells in the workbook. 
The +ve side is that all the info for the sheet names and wether to hide 
other sheets is contained on a single worksheet (called Front Page)

can anybody help?

Thanks 

Scott

date: Tue, 8 Jul 2008 16:32:01 -0700   author:   Scott

Re: Hide a sheet with a variable name   

hi, Scott !

let's suppose in "Front Page" sheet you put the names for the worksheets to hide/show in B3:B4 range

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myCell As Range
  Application.ScreenUpdating = False
  If Target.Address <> "$B$8" Then Exit Sub
  For Each myCell In Range("b3:b4")
    Worksheets(myCell.Text).Visible = Range("b8") <> ""
  Next
End Sub

hth,
hector.

__ OP __
> I am trying to hide a sheet(s) based on a cell value. I have found an old post thats uses:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    Dim sh As Worksheet
>    If Not Application.Intersect(Range("B8"), Target) Is Nothing Then
>        For Each sh In Sheets(Array("Sheet2"))
>            If Target.Value = "" Then sh.Visible = xlSheetHidden
>            If Target.Value <> "" Then sh.Visible = xlSheetVisible
>        Next sh
>    End If
> End Sub
>
> and this works perfectly. EXCEPT... my problem is that my sheet names change (in this case Sheet2)
> depending on the value of other cells in the workbook.
> The +ve side is that all the info for the sheet names and wether to hide other sheets
> is contained on a single worksheet (called Front Page)

date: Wed, 9 Jul 2008 00:13:42 -0500   author:   H?ctor Miguel

Re: Hide a sheet with a variable name   

G'Day Héctor

Thanks for the code it works quite well. I extended and moved the range from 
B3:B4 to A3:A10 and it still worked fine. However it (un)hides  all the 
sheets in the range. What I need to do is be able to show/hide individual 
sheets (names in range A3:A10) depnding on whether or not the corresonponing 
target cell in range B3:10 has an entry? ie if B7 is a null value (which is 
the default) then hide the sheet name, based on A7

Thanks 

Scott

"Miguel" wrote:

> hi, Scott !
> 
> let's suppose in "Front Page" sheet you put the names for the worksheets to hide/show in B3:B4 range
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>   Dim myCell As Range
>   Application.ScreenUpdating = False
>   If Target.Address <> "$B$8" Then Exit Sub
>   For Each myCell In Range("b3:b4")
>     Worksheets(myCell.Text).Visible = Range("b8") <> ""
>   Next
> End Sub
> 
> hth,
> hector.
> 
> __ OP __
> > I am trying to hide a sheet(s) based on a cell value. I have found an old post thats uses:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >    Dim sh As Worksheet
> >    If Not Application.Intersect(Range("B8"), Target) Is Nothing Then
> >        For Each sh In Sheets(Array("Sheet2"))
> >            If Target.Value = "" Then sh.Visible = xlSheetHidden
> >            If Target.Value <> "" Then sh.Visible = xlSheetVisible
> >        Next sh
> >    End If
> > End Sub
> >
> > and this works perfectly. EXCEPT... my problem is that my sheet names change (in this case Sheet2)
> > depending on the value of other cells in the workbook.
> > The +ve side is that all the info for the sheet names and wether to hide other sheets
> > is contained on a single worksheet (called Front Page) 
> 
> 
>

date: Thu, 10 Jul 2008 17:58:01 -0700   author:   Scott

Re: Hide a sheet with a variable name   

hi, Scott !

> Thanks for the code it works quite well.
> I extended and moved the range from B3:B4 to A3:A10 and it still worked fine.
> However it (un)hides all the sheets in the range.
> What I need to do is be able to show/hide individual sheets (names in range A3:A10)
> depnding on whether or not the corresonponing target cell in range B3:10 has an entry?
> ie if B7 is a null value (which is the default) then hide the sheet name, based on A7

perhaps...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myCell As Range
  Application.ScreenUpdating = False
  If Intersect(Target, Range("b3:b10")) Is Nothing Then Exit Sub
  For Each myCell In Intersect(Target, Range("b3:b10"))
    Worksheets(myCell.Offset(, -1).Text).Visible = myCell <> ""
  Next
End Sub

hth,
hector.

date: Thu, 10 Jul 2008 21:51:28 -0500   author:   H?ctor Miguel

Re: Hide a sheet with a variable name   

Héctor 

Thanks a lot it works perfectly

Scott

"Héctor Miguel" wrote:

> hi, Scott !
> 
> > Thanks for the code it works quite well.
> > I extended and moved the range from B3:B4 to A3:A10 and it still worked fine.
> > However it (un)hides all the sheets in the range.
> > What I need to do is be able to show/hide individual sheets (names in range A3:A10)
> > depnding on whether or not the corresonponing target cell in range B3:10 has an entry?
> > ie if B7 is a null value (which is the default) then hide the sheet name, based on A7
> 
> perhaps...
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>   Dim myCell As Range
>   Application.ScreenUpdating = False
>   If Intersect(Target, Range("b3:b10")) Is Nothing Then Exit Sub
>   For Each myCell In Intersect(Target, Range("b3:b10"))
>     Worksheets(myCell.Offset(, -1).Text).Visible = myCell <> ""
>   Next
> End Sub
> 
> hth,
> hector. 
> 
> 
>

date: Fri, 11 Jul 2008 00:21:01 -0700   author:   Scott