Hiding and Unhiding Worksheets

[Guest]

How do I hide any worksheets that are hidden. In some of my macros, I am
creating worksheets in a workbook but at certain instances all of these
worksheets may not be created and others may. I want to just specify unhide
all or hide all.

 

[Tom Ogilvy]

You can't hide all, since at least one worksheet needs to be visible all the
time.

Dim sh as worksheet
list = Array(1,5,8,9,10)
for i =lbound(list) to ubound(list)
set sh = Nothing
On Error Resume Next
set sh = worksheets(list(i))
On Error goto 0
if not sh is nothing then
sh.Visible = xlSheetVisible
Next

 

[Guest]

Also, I can't name the Worksheets because at times the names will be
different as I continue creating this tool. So basically what I want to do
is to say hide all worksheets except Vendor Worksheet or Merchant Worksheet
or Vendor Worksheet and Procurement Worksheet! Thanks Again

 

[Dave Peterson]

Maybe...

Option Explicit
sub testme()

Dim sh as worksheet
worksheets("Merchant").visible = xlsheetvisible
worksheets("vendor").visible = xlsheetvisible
Worksheets("procurement").visible = xlsheetvisible

for each sh in activeworkbook.worksheets
select case lcase(sh.name)
case is = "merchant","vendor","procurement"
'do nothing
case else
sh.visible = xlsheethidden
end select
next sh

end sub

 

[Guest]

Maybe I can be more explicit. I want to unhide the Vendor Worksheet only
(In this case it could be a Merchant Worksheet, Or a Procurement Worksheet)
I don't want to specify names of other worksheets that need to be hidden for
some may not be created as yet. I want to specify one sheet to be visible in
separate macros. So one macro would say unhide the Vendor Worksheet and hide
the rest. Another might say unhide the Merchant Worksheet and the Vendor
Worksheet and hide the rest.

 

[Executor]

Hi Rob,

Maybe this works:

Sub UnhideSheet(strNamesToUnhide As String)
' parameter strNamesToUnhide is spaceseparated list of
' sheetnames to unhide

Dim shLoop As Worksheet
Dim iPos As Integer


' make sure the first named sheet is visible
iPos = InStr(1, strNamesToUnhide, " ", vbTextCompare)
If iPos = 0 Then
' only one sheet in the list
Worksheets(strNamesToUnhide).Visible = xlSheetVisible
Else
' use only the first name
Worksheets(Left(strNamesToUnhide, iPos - 1)).Visible =
xlSheetVisible
End If

' Loop thue all sheets to hide / unhide
For Each shLoop In ActiveWorkbook.Worksheets
If InStr(1, strNamesToUnhide, shLoop.Name, vbTextCompare) = 0 Then
shLoop.Visible = xlSheetHidden
Else
shLoop.Visible = xlSheetVisible
End If
Next shLoop
End Sub


Sub TestMe1()
Call UnhideSheet("Sheet1")
End Sub

Sub TextMe2()
Call UnhideSheet("Sheet2 Sheet3")
End Sub


Executor