Unable to unhide worksheets

[reitzen]

My workbook is not protected. I have about 20 out of 45 worksheets that are hidden (25 worksheets visible at all times). When the workbook is closed, I unhide 6 of these worksheets so our servers can read the worksheets when the file is uploaded.

When I try and unhide using

Worksheets("Sheet1").Visible = xlSheetVisible

, the error I get is:
Run-time error '1004': Application-defined or object-defined error.

When I try and unhide using the VBE properties window changing the Visible property from "0 -xlSheetHidden" to "-1 - xlSheetVisible", the error I get is:
Unable to set the Visible property of the Worksheet class.

When I try and unhide from the menu (ALT, T, T, P), I get nothing. No error message, nada. The "Unhide" option is grayed out on the tab's context menu (right-click on the tab).

The workbook has to be compatible with Excel 2000 and later, so it is an XLS file type. This seems to be happening mostly in Excel 2010, but it has been reported once or twice with 2007. It does not happen to every file. I have about 1,000 files distributed and this issue has come up about a dozen times.

Is this file corrupt? Is there something that I am missing?

Thanks
Rob

 

[Worf]

Hi Rob

1. Have you ever used the xlSheetVeryHidden option in this project? It can only be changed with VBA.

2. Give the following code a try, it's not dependent on the sheet names. It assumes you want to unhide all of them.

Sub Disclose()
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
    sht.Visible = xlSheetVisible
Next

End Sub

 

[reitzen]

Yes, I have one worksheet that I set to xlSheetVeryHidden while the user is using the workbook. I then set it to xlSheetHidden when closing the workbook.

This worksheet returns the same errors.

Thanks Worf!

Rob