Solved

Unhide or Make Visible  All Worksheets

Posted on 2010-11-16
9
365 Views
Last Modified: 2012-05-10
Is there a phrase that will make all Worksheets within a Workbook visible, or is there a Sub that will put all worksheets in an array and then make the array visible.

I have a number of  ways, or code snippets, in using loops that will make each worksheet visible, but with more emphasis being placed on the efficiency of code, is there a way to use one line that selects all worksheets and sets their value to xlsheetVisibel or .Visible = true.

Or, if a Sub was placed at the beginning of a procedure and did a count of the worksheets, could that me a method to use later on in setting them to visible.

Thanks,

Cook
0
Comment
Question by:Cook09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 34147694
You can either loop through all the worksheets and protect them one at a time, or you can store all the worksheets in an array, which you will still have to loop through to protect them all.

Alternatively, you can add a sheet event that once a sheet is selected it gets formatted.  But that even would have to be for every sheet.

So you'll have to loop through the sheets one way or another.
0
 

Author Comment

by:Cook09
ID: 34147724
So, even though Excel has a way of selecting all of the worksheets within a workbook,

"Worksheets.Select", there is no way making use of that to add a method, like Visible to them?
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 34147806
If you want to make them all invisible at once, then you can do something like this:
Worksheets(Array("Sheet1", "Sheet2")).Visible = xlHidden
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34147863
But then you have to know all the names. Looping isn't hard you just do.
Dim s As Worksheet

For Each s In Worksheets
  s.Visible = xlSheetVisible
Next

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34147872
It is not really inefficient to do it that way.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34147885
If it looks like it's running slow it's because of the i/o not the code. Turn screen updating off and back on.
Applicaton.ScreenUpdating = False
Dim s As Worksheet

For Each s In Worksheets
  s.Visible = xlSheetVisible
Next

Applicaton.ScreenUpdating = True

Open in new window

0
 

Author Comment

by:Cook09
ID: 34148498
I just got back...but shouldn't I be able to set a variable...arrVariable =worksheets(array...)  and then use that variable name to set the visible or hidden attribute?   arrVariable.visible=xlsheetvisible ?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34148716
If you could do that (and I don't think you can) it would do the same loop in the background so it isn't any more efficient. Running methods off arrays is the exact same as writing a loop.
0
 

Author Closing Comment

by:Cook09
ID: 34149797
Even though I didn't get the answer I was looking for, yet it came close.

The best I could come up with, with each element as the sheet name was:

Sheets(arrVar(2)).Visible = True

I tried to find a way of changing all of the arrVar's at once, but was unsuccessful., although it would seem as if there should be .

Thanks for the input.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question