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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,825
Office Version
  1. 2010
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
14,825
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. I appreciate your kind comments.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,300
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top