Macro to Unhide then Hide Sheets

S

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
I have 16 sheets and 4 additional sheets that will kind of 'Group' these 16sheets. For example: I have 'Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16'.
4 additional Sheets are: 'Group1', 'Group2', 'Group3', 'Group4'.

I need a help with macro so that when this workbook is open all 20 sheets ('Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16') will go into hiding and only 4 additional Sheets ('Group1', 'Group2', 'Group3', 'Group4') will be visible. Now, these 4 sheets will have the command button links to the following sheets:

Sheet 'Group1':'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Hide All'
Sheet 'Group2':'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Hide All'
Sheet 'Group3':'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Hide All'
Sheet 'Group4':'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Hide All'

This being said, when you click on each command buttons, the respective sheets will open up and when click on 'Hide All', all of the open sheets for that *additional sheet' for example sheet 'Group1' will go into hiding again. I know this is much of asking but I appreciate your help. Thank you.
 
Sort by date Sort by votes
Thank you Ian but that did not work for me. Like what I said...with VBA, when I oepn the file I want all 16 sheets to be hidden and become visible only when click on those respective buttons. And, to hide them all, click on 'Hide All' button again. This is what I am looking for. Thanks.
 
Upvote 0
Hi sjha,

Try the code below. It may help you on your way.

Unfortunately you can't hide ALL 20 sheets on opening the workbook - at least one sheet must be visible so I suggested having a "Main" sheet on which your buttons reside and show or hide sheets from there.

So the Workbook_Open code hides all sheets except "Main" and there are buttons on that sheet to hide/show the groups of sheets etc.

Code: 
Private Sub Workbook_Open()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
    If wsSheet.Name = "Main" Then
    Else
    wsSheet.Visible = False
    End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub

and in module1 have the following code

Code: 
Sub ShowAll_Click()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
        wsSheet.Visible = True
    Next wsSheet
Application.ScreenUpdating = True
End Sub
Sub HideAll_Click()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
    If wsSheet.Name = "Main" Then
    Else
    wsSheet.Visible = False
    End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub
Sub Group1_Click()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
       If wsSheet.Name = "Main" Or _
          wsSheet.Name = "Sheet1" Or _
          wsSheet.Name = "Sheet2" Or _
          wsSheet.Name = "Sheet3" Or _
          wsSheet.Name = "Sheet4" Then
            wsSheet.Visible = True
            Else: wsSheet.Visible = False
        End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub
Sub Group2_Click()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
       If wsSheet.Name = "Main" Or _
          wsSheet.Name = "Sheet5" Or _
          wsSheet.Name = "Sheet6" Or _
          wsSheet.Name = "Sheet7" Or _
          wsSheet.Name = "Sheet8" Then
            wsSheet.Visible = True
        Else: wsSheet.Visible = False
        End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub
Sub Group3_Click()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
       If wsSheet.Name = "Main" Or _
          wsSheet.Name = "Sheet9" Or _
          wsSheet.Name = "Sheet10" Or _
          wsSheet.Name = "Sheet11" Or _
          wsSheet.Name = "Sheet12" Then
            wsSheet.Visible = True
        Else: wsSheet.Visible = False
        End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub
Sub Group4_Click()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
       If wsSheet.Name = "Main" Or _
          wsSheet.Name = "Sheet13" Or _
          wsSheet.Name = "Sheet14" Or _
          wsSheet.Name = "Sheet15" Or _
          wsSheet.Name = "Sheet16" Then
            wsSheet.Visible = True
        Else: wsSheet.Visible = False
        End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub



Hope this helps

Seamus
 
Upvote 0
Actually - this code is shorter:

Code: 
Sub Group1_Click()
Dim ws As Worksheet
MySheets = Array("Main", "Sheet1", "Sheet2", "Sheet3", "Sheet4")
    Call Hide_Show(MySheets)
End Sub

Sub Group2_Click()
MySheets = Array("Main", "Sheet5", "Sheet6", "Sheet7", "Sheet8")
    Call Hide_Show(MySheets)
End Sub

Sub Group3_Click()
MySheets = Array("Main", "Sheet9", "Sheet10", "Sheet11", "Sheet12")
    Call Hide_Show(MySheets)
End Sub

Sub Group4_Click()
MySheets = Array("Main", "Sheet13", "Sheet14", "Sheet15", "Sheet16")
    Call Hide_Show(MySheets)
End Sub

Function Hide_Show(MySheets)
Application.ScreenUpdating = False
For Each ws In Sheets
    X = Application.Match(ws.Name, MySheets, 0)
    If Not IsError(X) Then
    ws.Visible = True
    Else
    ws.Visible = False
    End If
Next ws
Application.ScreenUpdating = True
End Function

with a function at the end that all 4 subs use

Seamus
 
Upvote 0
Thank you. Yes, I like the second one better. Yet, two questions.

(1) Notices that with each click the previous 'open' sheets go into hiding again. How can I keep that open regardless of how many of sheets I open?
(2) When I open the workbook, all the files are visible unless I click on that particular button. And, I want all of the sheets invisible except "main" sheet.

If we can take care of these two...I think we are getting there. Thank you very much!!
 
Upvote 0
Hi,

Not sure I understand:

(1) Notices that with each click the previous 'open' sheets go into hiding again. How can I keep that open regardless of how many of sheets I open?
Click to expand...

Currently the code does as follows

Click Group1 - result is Sheets 1-4 visible - the rest invisible
Click Group2 - result is Sheets 5-8 visible - the rest invisible
Click Group1 - result is Sheets 9-12 visible - the rest invisible
Click Group1 - result is Sheets 13-16 visible - the rest invisible

Do you mean that after clicking Group2 (which will show sheets 5-8) you ALSO want to still see Sheets 1-4?

(2) When I open the workbook, all the files are visible unless I click on that particular button. And, I want all of the sheets invisible except "main" sheet.
Click to expand...

Sorry about that. You need to place the following code in the ThisWorkbook_Open part of the VBE screen

Code: 
Private Sub Workbook_Open()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
    If wsSheet.Name = "Main" Then
    Else
    wsSheet.Visible = False
    End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub

Hope that helps

Seamus
 
Upvote 0
Thank you very much for your reply. Below are answers of your questions:

Do you mean that after clicking Group2 (which will show sheets 5-8) you ALSO want to still see Sheets 1-4?

My comment: Yes, that is correct. In addition, instead of just unhiding the sheet, how can this be possible that it actually opens up the sheet?

(2) When I open the workbook, all the files are visible unless I click on that particular button. And, I want all of the sheets invisible except "main" sheet.

My comment: This works great for one sheet "Main". What if I want two sheets "Main" and "Main2" to be visible at all times i.e. when you open up the workbook? I tried to modify the code you suggested but it did not work for me.

Again, my sincere *Thank you* to you for your time and help!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Macro VBA hide/unhide with veryHidden option and VBA password
Replies
1
Views
855
alexbw
A
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
2K
kevin9999
K
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
1K
USNA91
U
H
  • Question Question
Hide/Unhide Columns using VBA
Replies
8
Views
848
Joe4
Joe4
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
588
NdNoviceHlp
NdNoviceHlp

Share this page

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