Hiding and Unhiding Worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 12 Jan 2006 18:16 GMT

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.

Reply to this Message

Tom Ogilvy - 12 Jan 2006 18:25 GMT

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

Signature

REgards,
Tom Ogilvy

> 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.

Reply to this Message

Rob - 12 Jan 2006 19:30 GMT

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

> You can't hide all, since at least one worksheet needs to be visible all the
> time.
[quoted text clipped - 15 lines]
> unhide
> > all or hide all.

Reply to this Message

Dave Peterson - 12 Jan 2006 20:14 GMT

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

> 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
[quoted text clipped - 24 lines]
> > unhide
> > > all or hide all.

Signature

Dave Peterson

Reply to this Message

Rob - 13 Jan 2006 13:52 GMT

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.

> You can't hide all, since at least one worksheet needs to be visible all the
> time.
[quoted text clipped - 15 lines]
> unhide
> > all or hide all.

Reply to this Message

Executor - 25 Jan 2006 11:11 GMT

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

Reply to this Message