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!