How do you unhide hidden worksheets then rehide?

[qjd2004]

Hiya,

trying to unhide all the worksheets in a workbook. My code skips from 1 wb to another and when in a workbook often switches between worksheets to gather and colate data.

Having hidden worksheets is causing me problems, but the specification I'm working to demands that they remain hidden except when being accessed by the code. I'm trying to unhide all wsheets and can't get it to work.

I've tried 2 methods:

Application.Workbooks("Run.xls").Worksheets.Visible = True

and whilst in the workbook Run.xls is the active wb

    With ActiveWorkbook.Worksheets
        .Visible = True
    End With

Both approaches yield the error message Method 'Visible' of object 'Sheets' Failed.

Can someone help please, I'm starting to want to pull my hair out!

 

[DrBowes]

Try:

Worksheets("Sheet1").Visible = xlSheetVisible
Worksheets("Sheet1").Visible = xlSheetHidden
Worksheets("Sheet1").Visible = xlSheetVeryHidden

or in your case

With ActiveWorkbook.Worksheets
.Visible = xlSheetVisible
End With

 

[DrBowes]

NB Very Hidden is useful as these sheets can only be made visible with code.

 

[Loomah]

Hi
Try this

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[qjd2004]

Thanks guys,

you're all superstars! It works like a treat and the xlVeryHidden is really useful to me as it's what i wanted to lock the user out of those sheets as well.

Thanks all!

 

[Wicca]

Allow me to add my thanks also. I have had a problem with users unhiding sheets and moving the active cell

They have created havoc without realising what they were doing

Thank you all

Wicca

 

[SkipVought]

Hi,

Two suggestions...

1) try to code using the Activate and Select methods as LITTLE asa possible -- they SLOW DOWN processing

faq707-4105 How Can I Make My Code Run Faster?

2) there is a THIRD Visible state for Worksheet. It is Very Hidden. the Very Hidden state can ONLY be assigned in either the VB editor of via code. use Very Hidden for sheets that the user should NEVER view.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884