Hiding Sheets

E

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!
 
S

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
 
D

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
 

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