Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show hidden worksheets (2002)

    I have a workbook where 2 worksheets are set to xlSheetVeryHidden.

    Format | Sheet | Unhide is greyed out.

    Changing the properties in the VBE results in error: Unable to set the Visible property of the Worksheet class.

    I have tried executing the following code:

    Sub AllSheetsVisible()

    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
    sh.Visible = xlSheetVisible
    Next sh
    Set sh = Nothing

    End Sub

    This results in Run-time error 1004.

    What am I missing??

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Show hidden worksheets (2002)

    Test for .ProtectContents, and if False then use sh.Visible = True

    (Are you sure that xlSheetVisible is a valid constant?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hidden worksheets (2002)

    Thanks John.
    but......

    Sub AllSheetsVisible2()

    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
    sh.Protection = False
    sh.ProtectContents = False
    sh.Visible = True
    Next sh
    Set sh = Nothing

    End Sub

    Now I get compile error "Can't assign to read-only property" @ .ProtectContents =

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Show hidden worksheets (2002)

    Sorry, I assumed you would understand how .ProtectContents works - you can't simply reset it, you have to test for it (or feed it the Password). FWIW, here is the version I use, I don't think I have had any trouble with it:

    Sub UnhideAllSheets()
    Dim wks As Worksheet
    Application.ScreenUpdating = False
    If Not ActiveWorkbook.ProtectStructure Then
    For Each wks In ActiveWorkbook.Sheets
    With wks
    If Not .ProtectContents Then
    If Not .Visible Then .Visible = True
    End If
    End With
    Next
    End If
    Set wks = Nothing
    Application.ScreenUpdating = True
    End Sub

    Does that help?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hidden worksheets (2002)

    Thanks.
    The code executed, with no result so i stepped through. It jumped from If straight to End If, bypassing the main code. I tried commenting out the If-End If, which executed all, but with no result. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Show hidden worksheets (2002)

    Then they may be Password protected, in which case you may have to use the Immediates Window with the appropriate sheet name:

    ThisWorkbook.Worksheets("mysheetname").Visible = True

    (I realize now that I'm a bit rusty, but I recall that this is a Catch-22 with xlVeryHidden and password protection - the macro has to first unprotect it, since you can't unprotect it manually, because you can't get to it manually! Perhaps someone will correct me on this.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hidden worksheets (2002)

    I appreciate your patience John.

    I've never used the Immediate window before. I copied and pasted the instruction in (replacing the sheet name), hit enter.....

    Unable to set the Visible property of the Worksheet class <img src=/w3timages/censored.gif alt=censored border=0> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show hidden worksheets (2002)

    Try adding:

    ActiveWorkbook.Unprotect

    at the start of your code, it sounds very much like you have protected the workbook not the worksheets
    Jerry

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hidden worksheets (2002)

    You are very much correct sir... And guess who does'nt have the password.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show hidden worksheets (2002)

    Hi Nathan
    I am working in Excel 2003 and find that even with a password protected workbook the following code exposes all worksheets. You might want to give it a try.<div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Sub</font color=blue> ExposeSheets()
    <font color=blue>Dim</font color=blue> i <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>For</font color=blue> i = 1 <font color=blue>To</font color=blue> Sheets.Count
    <font color=blue>With</font color=blue> Sheets(i)
    Worksheets(.Name).Visible = <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    <font color=blue>Next</font color=blue> i
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue></font color=black></code></div hiblock>
    Regards
    Don

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Show hidden worksheets (2002)

    Got called away on paid work - I think Jezza has identified the problem. FWIW I was able to use the immediates window to make a protected sheet visible from XLVeryHidden, in an unprotected WB. See attached - sheet Password is VegasNath. In the Immediates Window I used:

    Activeworkbook.Worksheets("sheet2").Visible = xlVeryHidden
    Activeworkbook.Worksheets("sheet2").Visible = True

    So a sheet can be protected and still be un-xlVeryHidden by code.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show hidden worksheets (2002)

    You might want to take a look here.

    Apologies for my previous post I was certain that when I tested the code, the workbook was protected. I was wrong.
    Regards
    Don

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show hidden worksheets (2002)

    Hi Don, Thanks for the link, I will check it out and report back, on a different project at the mo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •