VBA code that Unhides sheets that are hidden using sht.Visible = xlSheetVeryHidden

[simora]

I have hidden some worksheets using xlSheetVeryHidden. Now when I try to unhide a specific sheet, and selecting it, but it keeps giving me a subscript out of range error.
How can I unhide the worksheet.

I've tried
Worksheets("Home-Page").Select
Worksheets("Home-Page").Visible = True
Worksheets("Home-Page").xlSheetVeryHidden = False
Worksheets("Home-Page").unhide = True

[simora]

Now I'm getting a diffrent error:
Unable to set visible property of the worksheet class

[SamT]

WorkSheets("Home-Page").Visible = [One of the below]


xlSheetHidden


xlSheetVisible


xlSheetVeryHidden




In the VBA Editor, place the cursor inside or next to the word "Visible" and press F1 for help on the Visible Property.

[p45cal]

Your second line should work:
Worksheets("Home-Page").Visible = True
(substitute xlSheetVisible for True if it's being picky),
but if you get a subscript out of range error then you've got the name of the sheet wrong.

[simora]

Thanks for the ideas, code and suggestions.
I did use a crude solution, after all of the suggested ones did not work.

On this one: Worksheets("Home-Page").Visible = xlSheetVisible I checked and xlSheetVisible had a value of -1

What I finally did was to Unhide ALL of the worksheets, and then individually hid them one at a time except for the Home-Page.
This works, however, I now get a Subscript out of range error when I start the workbook.
I've checked WorkBook open and Before Close code. No luck finding anything that remotely looks like it changed.

[Aussiebear]

Please attach an empty workbook based on your current workbook settings? Are you sure the name of the sheet is correct?

[simora]

Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear) : I would normally do that, but this workbook is way too huge.

[Bob Phillips]

Maybe try



ThisWorkbook.Worksheets("Home-Page").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Home-Page" Then

ws.Visible = xlSheetVeryHidden
End If
Next ws

[simora]

Thanks xld (http://www.vbaexpress.com/forum/member.php?2139-xld)

I did the equivalent to that in the crude way that I described earlier. The home page is available etc..etc...
Is there a way to force the editor to show me where the error is located, i.e. & in which module.
The error Run-time error '9' Subscript out of range does not give me the option to Debug. Only End or Help
It appears at startup.
I'm hoping there's a Utility or something that is available.

[simora]

Thanks to Everyone who contributed.
I found that this was causing the error.



Application.WindowState = xlMaximized
If Worksheets("Home-Page").Visible = False Then
Worksheets("Home-Page").Visible = xlSheetVisible
End If


It worked for years earlier, and It was the only part of the code in that Workbook Open Sub.

The workaround was to include On Error Resume Next.
Hate having to do that, but ! !

[Aussiebear]

Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear) : I would normally do that, but this workbook is way too huge.

Really, an empty workbook with just the current workbook settings is too large for here? I'm confident you could manage this, including the code you are using to set or adjust the sheet visible properties.

[simora]

Hi Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear) :
I almost always try to include a workbook but I felt that because so many of the sheets had related links etc...etc. that the only way to track down the problem was to have access to the whole workbook. All 30+ sheets.
The solution to unhide all the worksheets allowed to to see that was in fact the case. I needed to look at all the worksheets.
Had I designed this myself, I would've done things entirely different. Multiple people adding stuff over the years causes these type of issues.
Thanks for all your suggestions & solutions.

[Aussiebear]

Okay, but we are simply trying to second guess what's at your end of the monitor.

[simora]

Aussiebear (http://www.vbaexpress.com/forum/member.php?3907-Aussiebear): Trust me, I do understand.

& Thanks.