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

 

[Scott T]

The code only changes the visibility of one sheet. Did you modify the code? If so please post the modified code.

 

[AwesomeSteph]

The code only changes the visibility of one sheet. Did you modify the code? If so please post the modified code.

I have not. this is what is on the boilerplate tab. I don't know how to make the others automatically be hidden until the criteria on those tabs are met to view the corresponding sheets per the previous code in this thread.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If [B4].Value <> "" And [B6].Value <> "" And [B8].Value <> "" And [B14].Value <> "" And [B16].Value <> "" And [F4].Value <> "" And [G5].Value <> "" Then
    For i = 2 To 13
        Sheets(i).Visible = True
    Next i
        Else
    For i = 2 To 13
         Sheets(i).Visible = xlVeryHidden
    Next i
End If
Application.ScreenUpdating = True
End Sub

 

[Scott T]

This should hide all sheets when the file is opened except start. Change to match your sheet name.

Private Sub Workbook_Open()Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "[COLOR=#ff0000]start[/COLOR]" Then
        ws.Visible = xlSheetHidden
    End If
Next ws


End Sub

Your code is looping through all sheets and unhiding them as long as the if statement is true. You would need to change the code to something like what posted to unhide a sheet based on what was entered.

 

[AwesomeSteph]

Thank you Scott, that makes more sense. I am learning as I go so this forum is so very helpful!

 

[AwesomeSteph]

This should hide all sheets when the file is opened except start. Change to match your sheet name.

Private Sub Workbook_Open()Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "[COLOR=#ff0000]start[/COLOR]" Then
        ws.Visible = xlSheetHidden
    End If
Next ws


End Sub

Your code is looping through all sheets and unhiding them as long as the if statement is true. You would need to change the code to something like what posted to unhide a sheet based on what was entered.

Is there a way to use my code but make the "For i = 2 To 13" statement just the sheets that I want to become un-hidden instead of all 2 through 13 for instance only sheets 2, 3, 4, 6, 7, 8 and 13?

 

[Fluff]

How about

For ws = 2 To Worksheets.Count
    Select Case ws
        Case 2 To 4, 6 To 8, 13
            Worksheets(ws).Visible = True
        Case Else
            Worksheets(ws).Visible = False
    End Select
Next ws