How to unhide Sheets based on 3 cell values

[Boris Smits]

E.g.
I'm in worksheet "Main" (all other sheets are hidden), in Cell A1 I type "SHEET 1", in A2 I type "SHEET 2" and in A3 I type "SHEET 3". How can I unhide SHEET 1, SHEET 2 en SHEET 3 with a command button (in VBA)? Of course if in A1 is typed "SHEET 4" then SHEET 4 must be unhidden.

So the value that is put in in cell A1, A2 en A3 must be used to open the corresponding Worksheets.

Hope someone can help me, I've searched for hours couldn't find the wright answer. Thank you!

[Kenneth Hobs]

Welcome to the forum!

Private Sub CommandButton1_Click()    
    Dim c As Range
    On Error Resume Next
    For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        Worksheets(c.Value).Visible = xlSheetVisible
    Next c
End Sub

[Boris Smits]

Thanks very much!

[Boris Smits]

But what if SHEET 1 is already vissible and SHEET 1 is not filled in in A1 to A999? Is it possible to hide SHEET 1? So na matter what, when you push the button ONLY the sheets mentioned in A1 to A999 must be visible, all others invisible. Is that possible?

[paulked]

You could hide them all except the current worksheet first:

Sub pk1()
    Dim ws As Worksheet, c As Range
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
    On Error Resume Next
    For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        Worksheets(c.Value).Visible = xlSheetVisible
    Next c
End Sub

[Boris Smits]

Thanks again! Next time I'm not going to search the internet for hours!