Macro to Unhide then Hide Sheets

[sjha]

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.

 

[sosullivan]

Sorry sjha,

I'm still struggling a bit to understand what you actually want.

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?

Does this mean that Group2 button should show sheets 5-8 AS WELL as any other sheets that happen to be unhidden at that point?

So let's say you click Group1 - this unhides sheets 1-4; then you click Group2 - should this unhide sheest 5-8 AND leave 1-4 unhidden as well?

If that is the case then the code should do the following:

Group1 : show sheets 1-4 - the rest hidden
Group2 : show sheets 1-4 AND 5-8 - the rest hidden
Group3 : show sheets 1-4 AND 5-8 AND 9-12 - the rest hidden
Group4 : show ALL sheets

or am I wrong?

What if I want two sheets "Main" and "Main2" to be visible at all times i.e. when you open up the workbook?

If this is the case the modify the Workbook_Open code as follows:

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

You must, of course, have a sheet called "Main2" for the above code to run.

It might ber good I were able to see a mock up workbook if you have one. You might want to send me a message and a book could be emailed to me if you wish

Seamus

 

[deckerp]

hi,
I am using this code but get errors.

In my sheet "WORKSPACE" I have created activeX buttons and assigned following code:

Private Sub Button_FloatReconsiliation_Click()
MySheets = Array("FLOAT")
    Call Hide_Show(MySheets)
End Sub
Private Sub Button_OperationsReport_Click()
MySheets = Array("OPERATIONS_REPORT")
    Call Hide_Show(MySheets)
End Sub
Private Sub ButtonBusinessDone_Click()
MySheets = Array("BUSINESS_DONE")
    Call Hide_Show(MySheets)
End Sub

Module4

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

error message:
Run-time error '1004': unable to set the Visible property of the Worksheet class.

debug highlights: ws.Visible = False

 

[deckerp]

solved.

 

[polkee2010]

Wonderful and awesome code
can i ask another question ??
if i want to hide all sheets except main sheet and these sheets contain specific word e.g. "jan" and the shees like: jan_m1,jan_31,jan_43 ,...etc
what will the VBA code will be???

 

[PATTANAM]

I have gone through all Coding and It is very useful Thank you!.. I need further help onthis,

Now I have Requirement In One Sheet two dropdown list/Data Validations.

First one is working as specified.. which set dropdown list Range ("15")..

Same way I have same sheet different requirement that is Range("L36") it not working.. please help.

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Range("G15")
Case "(Select Order Type)"

Dim wsSheet5 As Worksheet
Application.ScreenUpdating = False


Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Range("L36")


Case "(Select Order Type)"

Dim wsSheetA5 As Worksheet

Application.ScreenUpdating = False

For Each wsSheetA5 In Worksheets
If wsSheetA5.Name = "Home" Or _
wsSheetA5.Name = "Order Entry Worksheet" Or _
wsSheetA5.Name = "Version Control" Then
'wsSheet.Name = "Sheet4" Then

wsSheetA5.Visible = True

Else: wsSheetA5.Visible = False

End If

Next wsSheetA5

Application.ScreenUpdating = True