Hide and/or Unhide Worksheets

[Rob]

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.

[michael fuller]

Hi Rob

You could try this.

Sub Merchant_visible()

Dim ws As Worksheet

Sheets("merchant").Visible = -1
For Each ws In Worksheets
If ws.Name <> "mercnant" Then
Sheets(ws.Name).Visible = 2
End If
Next
End Sub

"Rob" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>

[PCLIVE]

One way to hide all except "Vendor".

Sub HideAllExceptVendor()
Sheets("Vendor").Visible=True
For i = 1 To Worksheets.Count
If Sheets(i).Name <> "Vendor" Then
Sheets(i).Visible = False
End If
Next
End Sub


To Hide all except "Vendor" and "Merchant"

Sub HideAllExceptVendorAndMerchant()
Sheets("Vendor").Visible = True
Sheets("Merchant").Visible = True
For i = 1 To Worksheets.Count
If Sheets(i).Name <> "Vendor" Then
If Sheets(i).Name <> "Merchant" Then
Sheets(i).Visible = False
End If
End If
Next
End Sub

HTH,
Paul



"Rob" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>

[Jim Thomlinson]

Give this a try...

Sub Test()
Call HideAllSheets("That")
End Sub

Private Sub HideAllSheets(ByVal SheetName As String)
Dim wks As Worksheet

If SheetExists(SheetName) Then
Sheets(SheetName).Visible = xlSheetVisible
For Each wks In Worksheets
If wks.Name <> SheetName Then wks.Visible = xlSheetHidden
Next wks
Else
MsgBox "Error on sheet name."
End If
End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

--
HTH...

Jim Thomlinson


"Rob" wrote:

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

[GB]

I would do it simply that hide all sheets, then unhide only the one(s) you
want to, so no if statement needed in the for loop. Just hide 'em all then
unhide the desired.
Before that hiding or unhiding the first time I would add:
application.screenupdating = false
And then after the changes have been made
application.screenupdating = true. That way the user doesn't see worksheets
disappearing and reappearing, just a flash from where they are to where they
are going.


"PCLIVE" wrote:

> One way to hide all except "Vendor".
>
> Sub HideAllExceptVendor()
> Sheets("Vendor").Visible=True
> For i = 1 To Worksheets.Count
> If Sheets(i).Name <> "Vendor" Then
> Sheets(i).Visible = False
> End If
> Next
> End Sub
>
>
> To Hide all except "Vendor" and "Merchant"
>
> Sub HideAllExceptVendorAndMerchant()
> Sheets("Vendor").Visible = True
> Sheets("Merchant").Visible = True
> For i = 1 To Worksheets.Count
> If Sheets(i).Name <> "Vendor" Then
> If Sheets(i).Name <> "Merchant" Then
> Sheets(i).Visible = False
> End If
> End If
> Next
> End Sub
>
> HTH,
> Paul
>
>
>
> "Rob" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
>
>
>

[GB]

This works to show only one sheet. Though you could possible setup something
like a list of sheets, and pass in a class module that contains an array.
And the if statement would check to see if any sheet that is in the array is
the current sheet, then don't hide it.

I like the solution below though, it's portable, and is designed for
expansability.


"Jim Thomlinson" wrote:

> Give this a try...
>
> Sub Test()
> Call HideAllSheets("That")
> End Sub
>
> Private Sub HideAllSheets(ByVal SheetName As String)
> Dim wks As Worksheet
>
> If SheetExists(SheetName) Then
> Sheets(SheetName).Visible = xlSheetVisible
> For Each wks In Worksheets
> If wks.Name <> SheetName Then wks.Visible = xlSheetHidden
> Next wks
> Else
> MsgBox "Error on sheet name."
> End If
> End Sub
>
> Public Function SheetExists(SName As String, _
> Optional ByVal Wb As Workbook) As Boolean
> 'Chip Pearson
> On Error Resume Next
> If Wb Is Nothing Then Set Wb = ThisWorkbook
> SheetExists = CBool(Len(Wb.Sheets(SName).Name))
> End Function
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Rob" wrote:
>
> > 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.
> >
> >