Macro to unhide a sheet based on a cell value.

dejeud

New Member
Joined
Aug 10, 2012
Messages
15
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.


Code:
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

New Member
Joined
Aug 10, 2012
Messages
15
Thank you so much.
Works perfect and more than I was hoping for.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. I appreciate your kind comments.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top