Code to make only certain sheets visible


M

Michael

Hello everyone,

I have a workbook with 3 worksheets (Sheet1, Sheet2, Sheet3) and 2
pivotcharts (Chart1, Chart2). I want to write a sub that when it is called
it cycles through all of the worksheets and charts (all of the workbook
objects) and toogle sheet visibility. I want to have only one of the
following objects visible every time my code runs:

- Sheet1
- Sheet2
- Chart1
- Chart2

My sample code is the one below but its not even close. Can I get some help?

Sub ToogleSheetVisiblity()
'You must leave at least one Sheet visible
Dim wsSheet As Worksheet
On Error Resume Next 'If code tries to hide all Sheets
For Each wsSheet In Worksheets
If wsSheet.Name = "Sheet1"
Worksshets(wsSheet.Index - 1).Visible = False
wsSheet.Visible = True
End If
Next wsSheet
On Error GoTo 0 'One sheet will always be left visible
End Sub


Thanks
Michael
 
Ad

Advertisements

D

Dave Peterson

How about:

Option Explicit
Sub ToogleSheetVisiblity()

Dim sCtr As Long
Dim NextIndex As Long

NextIndex = ActiveSheet.Index + 1

If NextIndex > Sheets.Count Then
NextIndex = 1
End If

Sheets(NextIndex).Visible = xlSheetVisible

For sCtr = 1 To Sheets.Count
If sCtr = NextIndex Then
'do nothing
Else
Sheets(sCtr).Visible = xlSheetHidden 'xlSheetVeryHidden
End If
Next sCtr
End Sub
 
B

Bob Phillips

Dim sh As Object
Dim sh2 As Object
For Each sh In Sheets(Array("Sheet1", "Sheet2", "Chart1", "Chart2"))
If sh.Visible = xlSheetVisible Then
For Each sh2 In Sheets(Array("Sheet1", "Sheet2", "Chart1",
"Chart2"))
If sh.Name <> sh2.Name Then
sh2.Visible = xlSheetHidden
End If
Next sh2
Exit For
End If
Next sh

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Michael

Dave I tried your code below and it worked like a charm. Kudos!

Dave, I want to make a minor modification to your code below and perhaps you
might be able to help.

I have a workbook with 3 worksheets (Sheet1, Sheet2, Sheet3) and 3
PivotCharts (Chart1, Chart2, Chart3). Your code below as is cycles through
all of the worksheets (6 sheets). What I need, is to modify the code below
to cycle through some specific sheets only. Specifically I want to cycle
through Sheet1, Sheet3, Chart1 and Chart2.

Dave any suggestions?

Thanks in advance
Michael
 
D

Dave Peterson

Maybe something like:

Option Explicit
Sub ToogleSheetVisiblity()

Dim sCtr As Long
Dim NextIndex As Long
Dim myIndexes() As Long
Dim mySheetNames As Variant
Dim AnErrorWasFound As Boolean
Dim VisSheetCtr As Long

mySheetNames = Array("Sheet1", "Sheet3", "Chart1", "Chart2")

ReDim myIndexes(LBound(mySheetNames) To UBound(mySheetNames))

AnErrorWasFound = False
On Error Resume Next
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
myIndexes(sCtr) = Sheets(mySheetNames(sCtr)).Index
If Err.Number <> 0 Then
AnErrorWasFound = True
Exit For
Err.Clear
End If
Next sCtr
On Error GoTo 0

If AnErrorWasFound Then
MsgBox "Design error in sheet names!"
Exit Sub
End If

VisSheetCtr = -999 'can't happen
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If Sheets(mySheetNames(sCtr)).Visible = xlSheetVisible Then
'found it
VisSheetCtr = sCtr
Exit For
End If
Next sCtr

Select Case VisSheetCtr
Case Is = -999, UBound(myIndexes)
NextIndex = LBound(myIndexes)
Case Else
NextIndex = VisSheetCtr + 1
End Select

Sheets(myIndexes(NextIndex)).Visible = xlSheetVisible

For sCtr = LBound(myIndexes) To UBound(myIndexes)
If sCtr = NextIndex Then
'do nothing
Else
Sheets(myIndexes(sCtr)).Visible = xlSheetHidden 'xlSheetVeryHidden
End If
Next sCtr
End Sub

If you want a different order of appearance, then change the order in the
array("... line.
 
M

Michael

Thanks a bunch! It worked like a charm :) Your help is greatly appreciated.

- Michael
 
Ad

Advertisements

Ad

Advertisements

D

Dave Peterson

If you see typose in my posteses, then they don't belngo to me. I jist kopied
them from the OP.

I vary rarely make those things.



Roger said:
Hi Dave
Sub ToogleSheetVisiblity
Your new competitor to Google, perchance?? <vbg>
Shhhh!!, don't tell Gord<g>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top