Cook09
asked on
Unhide or Make Visible All Worksheets
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
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
ASKER
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?
"Worksheets.Select", there is no way making use of that to add a method, like Visible to them?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
It is not really inefficient to do it that way.
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
ASKER
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=xlshee tvisible ?
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.
ASKER
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.
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.
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.