Hiding Sheets

[Eric]

Looking for some input on hiding sheets. I've got a
workbook with about 50 sheets. At any point in time,
what I'd like is to have just 1 sheet visible at a time
(depending what a user selects from a menu). What I've
been using so far is the code below:


Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
wsSheet.Visible = wsSheet.Name = "Sheet1"
Next wsSheet

End Sub

This works, but I'm not sure if this is the best way to
achieve what I want. The thing I don't like about this
method is I see my workbook hiding each sheet
individually for about 5 seconds. Is there a better way
to do this where I woulnd't get that?

Thanks!

 

[Sue Harsevoort]

If you set Application.ScreenUpdating = False before the loop and then set
it back to true after the loop you don't see the sheets hiding.

Sue

 

[greg]

does setting
Application.ScreenUpdating = False
before the For, help?

 

[Don Guillett]

right click on sheet tab>view code>copy/paste this>SAVE
Type in your menu (WATCH YOUR SPELLING)
Then just double click on the sheet to goto and hide all other except
Sheet1.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
For Each ws In Worksheets
If ws.Name = "Sheet1" Or ws.Name = Target Then
ws.Visible = True
Else
ws.Visible = False
End If
Next
End Sub

 

[Nigel]

Change to this.......

Sub ScreenUpdate ()

Dim wsSheet As Worksheet

Application.ScreenUpdating = False

For Each wsSheet In Worksheets
wsSheet.Visible = wsSheet.Name = "Sheet1"
Next wsSheet

Application.ScreenUpdating = True

End Sub