How To Hide/Unhide sheets based on a button click

**m2som** · 05-14-2017, 06:28 PM

Hello Guys, Please help me with the attached sheet, As I want to use the two buttons in the cover sheet to hide and unhide sheets on click, Like If I press case 1 then case 1 sheets will be shown and case two sheets will be hidden. Also I need to disable unhiding sheets to be only by pressing the button.

Regards

**alansidman** · 05-14-2017, 07:20 PM

Try this:

Option Explicit

Sub Oval1_Click()
Sheets("Case 1 Sheet").Visible = False
Sheets("Case 1 Sheet 2").Visible = False
Sheets("Case 2 Sheet").Visible = True
Sheets("Case 2 Sheet 2").Visible = True
End Sub
Sub Oval2_Click()
Sheets("Case 1 Sheet").Visible = True
Sheets("Case 1 Sheet 2").Visible = True
Sheets("Case 2 Sheet").Visible = False
Sheets("Case 2 Sheet 2").Visible = False
End Sub

https://www.mrexcel.com/forum/excel-...e-control.html

**mike7952** · 05-14-2017, 07:21 PM

One way

For Oval 1 macro

Const shCover As String = "Cover"
Sub Case1()
 Dim ws As Worksheet
 Application.ScreenUpdating = False
 For Each ws In Worksheets
    If ws.Name <> shCover Then
        If ws.Name Like "Case 1*" Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    End If
 Next
 Worksheets(shCover).Select
 Application.ScreenUpdating = True
End Sub

For Oval 2 macro

Sub Case2()
 Dim ws As Worksheet
 Application.ScreenUpdating = False
 For Each ws In Worksheets
    If ws.Name <> shCover Then
        If ws.Name Like "Case 2*" Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    End If
 Next
 Worksheets(shCover).Select
 Application.ScreenUpdating = True
End Sub

**Logit** · 05-14-2017, 07:30 PM

.
.
And still one more way :

Option Explicit

Sub ShowHideWorksheets1()
Dim Cell As Range
For Each Cell In Range("A1:A2")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

Sub ShowHideWorksheets2()
Dim Cell As Range
For Each Cell In Range("B1:B2")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

**Norie** · 05-14-2017, 07:54 PM

Was going to post some code for this but it's a bit late.

Anyway just curious, couldn't Application.Caller be used here?

How To Hide/Unhide sheets based on a button click

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How To Hide/Unhide sheets based on a button click

Re: How To Hide/Unhide sheets based on a button click

Re: How To Hide/Unhide sheets based on a button click

Re: How To Hide/Unhide sheets based on a button click

Re: How To Hide/Unhide sheets based on a button click

Thread Information

Users Browsing this Thread

Similar Threads

Toggle Button to hide unhide sheets

Required macro with button/option to hide unhide several sheets

[SOLVED] Using VBA to Hide and Unhide Columns with Click of Button

[SOLVED] Hide And Unhide Column With One Button,,,,Once Click Hide,,,,,Again Click Unhide

How to HIDE/UNHIDE Specific Sheets Using a TOGGLE Button?

Hide/unhide button based on data validations

[SOLVED] [SOLVED] create button that can hide/unhide sheets and hyperlink...

Bookmarks

Bookmarks

Posting Permissions