Keep lot of sheets Displayed and Hide Other
 

Hi all
many thanks for previous help.

My Workbook contain 30 sheets
Sheet 1 is summary Sheet contain Buttons to display and activate selected
sheet button and hide others.
I found that I need to exclude al lot of sheets from hiding (say from sheet2
to sheet6). I mean that I need it to be displayed always.

This Code Used in Summary sheet
--------------------------------------------
Sub DisplayWorksheet(strSheet As String)
'This will hide all other sheets except "Summary" and display strSheet
Application.ScreenUpdating = False
For Each wstemp In ActiveWorkbook.Sheets
If wstemp.Name < "SUMMARY" Then wstemp.Visible = False
Next
ActiveWorkbook.Sheets(strSheet).Visible = True
Application.ScreenUpdating = True
End Sub
--------------------------------
And this code to Activate and display for eatch
---------------------------------
Private Sub CommandButton1_Click()
DisplayWorksheet ("Sheet12")
Sheets("Sheet12").Activate
End Sub

Keep lot of sheets Displayed and Hide Other
 

Modified to suit your requirement. Test and feedback.

Add on the sheets which are to be visible always to the variable strSheets.

Sub DisplayWorksheet(strSheet As String)
'This will hide all sheets mentioned in strSheets (comma separated)
strSheets = "Summary,Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6 "
Application.ScreenUpdating = False
For Each wstemp In ActiveWorkbook.Sheets
If InStr(1, "," & strSheets & ",", "," & wstemp.Name & ",", _
vbTextCompare) = 0 Then wstemp.Visible = False
Next
ActiveWorkbook.Sheets(strSheet).Visible = True
Application.ScreenUpdating = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"NIDAL" wrote:

Hi all
many thanks for previous help.

My Workbook contain 30 sheets
Sheet 1 is summary Sheet contain Buttons to display and activate selected
sheet button and hide others.
I found that I need to exclude al lot of sheets from hiding (say from sheet2
to sheet6). I mean that I need it to be displayed always.

This Code Used in Summary sheet
--------------------------------------------
Sub DisplayWorksheet(strSheet As String)
'This will hide all other sheets except "Summary" and display strSheet
Application.ScreenUpdating = False
For Each wstemp In ActiveWorkbook.Sheets
If wstemp.Name < "SUMMARY" Then wstemp.Visible = False
Next
ActiveWorkbook.Sheets(strSheet).Visible = True
Application.ScreenUpdating = True
End Sub
--------------------------------
And this code to Activate and display for eatch
---------------------------------
Private Sub CommandButton1_Click()
DisplayWorksheet ("Sheet12")
Sheets("Sheet12").Activate
End Sub

Keep lot of sheets Displayed and Hide Other
 

I like to use Select Case for this type of code because it is so easy to add
or delete a condition.

The code I have provided uses the given worksheet name. However, you could
also use wstemp.CodeName in lieu of wstemp.Name. When in the VBA editor if
you look at the Project Explorer on the left of the screen, the CodeName is
displayed with the given sheet name in parenthesis. Initially the CodeName
and given sheet name are the same but if you change the given sheet name, the
CodeName remains unchanged. Using the CodeName means that if the user changes
a sheet name then the code will not fail because the CodeName remains
unchanged.

Sub DisplayWorksheet(strSheet As String)
'This will hide all other sheets except "Summary" and display strSheet
Application.ScreenUpdating = False

Select Case wstemp.Name
'Include all sheets to be visible by adding string with comma between
them.
Case "SUMMARY", "MYSHEET", "YOURSHEET"
wstemp.Visible = True

Case Else
'All other sheets not visible
wstemp.Visible = False

End Select

ActiveWorkbook.Sheets(strSheet).Visible = True
Application.ScreenUpdating = True
End Sub


--
Regards,

OssieMac


"NIDAL" wrote:

Hi all
many thanks for previous help.

My Workbook contain 30 sheets
Sheet 1 is summary Sheet contain Buttons to display and activate selected
sheet button and hide others.
I found that I need to exclude al lot of sheets from hiding (say from sheet2
to sheet6). I mean that I need it to be displayed always.

This Code Used in Summary sheet
--------------------------------------------
Sub DisplayWorksheet(strSheet As String)
'This will hide all other sheets except "Summary" and display strSheet
Application.ScreenUpdating = False
For Each wstemp In ActiveWorkbook.Sheets
If wstemp.Name < "SUMMARY" Then wstemp.Visible = False
Next
ActiveWorkbook.Sheets(strSheet).Visible = True
Application.ScreenUpdating = True
End Sub
--------------------------------
And this code to Activate and display for eatch
---------------------------------
Private Sub CommandButton1_Click()
DisplayWorksheet ("Sheet12")
Sheets("Sheet12").Activate
End Sub