Hiding sheets based on value

[jUStPunkin]

All,

As part of a larger project (and with some great help from this board), I've almost finished what I'm working on. I received this little bit of help earlier, where I want to hide some sheets and show others based on a specific criteria. I now realized that I actually have a second criteria which is important in determining what is hidden.

I have two values "G_CapturedAbove" and "G_TwoTiered". I have this little bit written to hide sheets based on the value of "G_Captured Above". What I need is to add is if "G_TwoTiered" = "Y", none of the sheets below are visible.

I feel like what I need to do is add another variable and set the visibility state based on both values, but I'm unsure how to do that. Can I add the variable and do an And function for each of those sheets? I tried, it didn't seem to work, but I may not have had it exactly correct.

    Dim IsNo As Boolean
        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
        Sheets("Spouts").Visible = IsNo
        Sheets("Spouts2").Visible = Not IsNo
        Sheets("Redistribution Calculations").Visible = Not IsNo

 

[Bosquedeguate]

You have 4 possible states

Book1
	A	B	C	D	E
1			Sheet Hidden
2	G_CapturedAbove	G_TwoTiered	Spouts	Spouts2	Redistribution Calculation
3	N	N	Visible	Hidden	Hidden
4	N	Y	Hidden	Hidden	Hidden
5	Y	Y	Hidden	Hidden	Hidden
6	Y	N	Hidden	Visible	Visible
Sheet1

    Dim IsNo As Boolean
     GCA = UCase(Range("G_CapturedAbove").Value) = "Y"
     GTT = UCase(Range("G_TwoTiered").Value) = "Y"
     If UCase(Range("G_TwoTiered").Value) = "Y" Then
        Sheets("Spouts").Visible = False
        Sheets("Spouts2").Visible = False
        Sheets("Redistribution Calculations").Visible = False
    Else  
        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
        Sheets("Spouts").Visible = IsNo
        Sheets("Spouts2").Visible = Not IsNo
        Sheets("Redistribution Calculations").Visible = Not IsNo
    End if

 

[jUStPunkin]

You have 4 possible states

Book1
	A	B	C	D	E
1			Sheet Hidden
2	G_CapturedAbove	G_TwoTiered	Spouts	Spouts2	Redistribution Calculation
3	N	N	Visible	Hidden	Hidden
4	N	Y	Hidden	Hidden	Hidden
5	Y	Y	Hidden	Hidden	Hidden
6	Y	N	Hidden	Visible	Visible
Sheet1

    Dim IsNo As Boolean
     GCA = UCase(Range("G_CapturedAbove").Value) = "Y"
     GTT = UCase(Range("G_TwoTiered").Value) = "Y"
     If UCase(Range("G_TwoTiered").Value) = "Y" Then
        Sheets("Spouts").Visible = False
        Sheets("Spouts2").Visible = False
        Sheets("Redistribution Calculations").Visible = False
    Else 
        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
        Sheets("Spouts").Visible = IsNo
        Sheets("Spouts2").Visible = Not IsNo
        Sheets("Redistribution Calculations").Visible = Not IsNo
    End if

I see where this is going, and it makes sense to me. I am getting a variable not defined error; I'm assuming I have to define GCA and GTT?

 

[Bosquedeguate]

I see where this is going, and it makes sense to me. I am getting a variable not defined error; I'm assuming I have to define GCA and GTT?

I think I cut and pasted the wrong code. Try this

    Dim IsNo As Boolean
    If UCase(Range("G_TwoTier").Value) = "Y" Then
        Sheets("Spouts").Visible = False
        Sheets("Spouts2").Visible = False
        Sheets("Redistribution Calculations").Visible = False
    Else
        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
        Sheets("Spouts").Visible = IsNo
        Sheets("Spouts2").Visible = Not IsNo
        Sheets("Redistribution Calculations").Visible = Not IsNo
    End if

 

[jUStPunkin]

they are not needed, I forgot to delete those two lines

Excellent! Thank you so much.