hide a sheet until criteria in a different sheet in a range is met at least once.

[AwesomeSteph]

I have a workbook with 16 sheets.
If [H18:H44] in sheet3 has an "I" or a "VOS" ((these are from a data validation drop down menu and are the users only options) bigger problem there is a "V" option as well not sure if that is going to make it impossible since VOS has a V in it.) I want sheet13 to be visible, if that range is either blank or including a "V" I want sheet13 to be hidden. What I have does not work (it has worked on other spreadsheets I have created so I am lost). Please help.

this is the sheet3 microsoft excel object.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If [H18:H44].Value = "I" Or [H18:H44].Value = "VOS" Then
Sheets(13).Visible = True
    Else
        Sheets(13).Visible = False
    End If
End If
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Please clarify: You want every cell in H18:H44 filled with either "I" or "VOS", else hide sheet13?

 

[AwesomeSteph]

If all cells in H18:H44 are empty keep sheet13 hidden.
If all cells in H18:H44 are "V" keep sheet13 hidden.

If any cell in H18:H44 has a "I" or "VOS" I need sheet13 to be visible.
There may be combination of all 3, if there is even one "I" or "VOS" I need sheet13.

 

[JoeMo]

Does this do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If [COUNTIF(H18:H44,"I")] > 0 Or [COUNTIF(H18:H44,"VOS")] > 0 Then
    Sheets("Sheet13").Visible = True
Else
    Sheets("Sheet13").Visible = False
End If
Application.ScreenUpdating = True
End Sub

 

[Scott T]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("H18:H44")) Is Nothing Then
Else
    If Application.CountIf(Range("H18:H44"), "I") > 0 Or Application.CountIf(Range("H18:H44"), "VOS") > 0 Then
        Worksheets(13).Visible = True
    Else
        Worksheets(13).Visible = False
    End If


End If


End Sub

 

[AwesomeSteph]

Does Sheet13 have to be categorized? This isn't working.

 

[AwesomeSteph]

This is causing sheet9 to be visible I'm very confused.

 

[Fluff]

If the sheet name is "Sheet13" use Joemo' code, but if you are talking about the sheet codename try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If [COUNTIF(H18:H44,"I")] > 0 Or [COUNTIF(H18:H44,"VOS")] > 0 Then
    Sheet13.Visible = True
Else
    Sheet13.Visible = False
End If
Application.ScreenUpdating = True
End Sub

 

[AwesomeSteph]

Thank you Fluff!!

 

[AwesomeSteph]

Ok I may be going overboard here but I have another questions about this code.
So initially ALL the sheets in this workbook are hidden except for the first. The first sheet has required fields for users to fill out in order to see/use the other sheets. It is a boilerplate for required forms for the users new work. So all the sheets appear hidden at first. Once I use the code above it works while I am in VBA but once I save and try to test it out as soon as I fill in the required criteria in the boilerplate all the sheets are visible after that. Should I alter the code on the first sheet in order to achieve what I am trying to do by keeping some hidden or should I rearrange the sheet numbers to have all the sheets I want to remain hidden after the fact?