Macro to unhide a sheet based on a cell value.

[dejeud]

I'd like to make a macro to unhide a sheet by double clcking on a cell.
The cell value would have the same name as the sheet.

let's say I have this line:

(A1) "Sheet1" : (A2) "NAME" : (A3) "NAME
So by double clicking any were in the row 1 would unhide sheet "SHEET1"

Also, is there a way to hide all sheets without listing each of their names in an array?

Thank you for all the help.

 

[DanteAmor]

Put the following code in the events of your "control" sheet.
In cell "A1" put the word "All", when you press double click any were in the row 1, all the sheets will be hidden, except the "control" sheet. From A2 down put the names of the sheets.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    wName = LCase(Cells(Target.Row, "A").Value)
    For Each sh In Sheets
        If wName = LCase("All") Then
            If sh.Name <> ActiveSheet.Name Then
                sh.Visible = 0
            End If
        Else
            If wName = LCase(sh.Name) Then
                sh.Visible = -1
            End If
        End If
    Next
End Sub

Note: To put the code in events, Right click the tab of the sheet you want this to work, select view code & paste the code into the window that opens up.

Go back to excel and double click on "All", then double click on any cell to show this sheet

 

[dejeud]

Thank you so much.
Works perfect and more than I was hoping for.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.