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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
I'm glad to help you. I appreciate your kind comments.
 
Upvote 0

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

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
Back
Top