Excel 2010 Document Inspector reports hidden sheets, not shown in VBA

Right-click on the name tab of any sheet. If you have any hidden sheets then Unhide ... will be enabled [it is otherwise greyed out].

Denis

If you have set a sheet's properties to xlVeryHidden then it will not enable the Hide ... entry but it will get reported by Document inspector.

But I do not understand why it does not appear in your VBA sheet lister, which is assume is along the lines of

Code:

Sub ListSheets()
Set NewSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
    NewSheet.Cells(i, 1).Value = Sheets(i).Name
Next i
End Sub

In any case, you could make even xlVeryHidden sheets visible by running

Code:

Sub UnhideSheets()
For Each Fred In Sheets
 Fred.Visible = True
Next Fred
End Sub

and this will allow you to check what is in them then delete them manually if you want to.

Denis

I do not understand what has caused this situation. I have never seen a VBA module treated as a worksheet.

It would be interesting to make another copy of the original to see what the macro ReportSheetVisibility does [see below].

Here are the same VBA procedures again plus some others that I have been playing with while thinking about this situation

Code:

Sub VeryHiddenTest()
' Creates a new worksheet, sets it to xlVeryHidden and writes some random values in some cells
' xlVeryHidden sheets do not enable the Unhide ... control in the sheets tab context manu
' but do get reported by the Document inspector
' - the only way to make a xlVeryHidden sheet visible again is by setting this property to True (the user cannot make the object visible through the UI).
Set NewSheet = Worksheets.Add
NewSheet.Visible = xlVeryHidden
NewSheet.Range("A1:D4").Formula = "=RAND()"
End Sub

Sub ListSheets()
' Creates a new sheet for logging then lists all sheets including hidden and xlVeryHidden ones in it
Set NewSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
    NewSheet.Cells(i, 1).Value = Sheets(i).Name
Next i
End Sub

Sub UnhideSheets()
' Unhides all sheets including xlVeryHidden ones
For Each Fred In Sheets
 Fred.Visible = True
Next Fred
End Sub

Sub ReportSheetVisibility()
' Report sheet visibility
' 0 is hidden
' -1 means visible
' 2 means xlVeryHidden
For Each Fred In Sheets
 MsgBox "Sheet name " & Fred.Name & " Sheet visibility " & Fred.Visible
Next Fred
End Sub

Sub UnhideVeryHiddenSheet()
' Unhides all xlVeryHidden sheets but not xlHidden ones
For Each Fred In Sheets
 If Fred.Visible = 2 Then Fred.Visible = True
Next Fred
End Sub

Denis

Just a wild shot in the dark ...

Didn't macros get recorded in worksheets before Excel switched to VBA?
- I can't think how far back this was. Possibly Excel 4 in the early 1990s.
- Of course, this would make no sense at all if your workbook had been created since, say, Excel 2007.

If your workbook is older than that I'd suggest
1 Create a new blank one then, with both open at once, use the sheet tab Move or copy ... to copy each sheet to the new blank workbook.
2 In the VBEditor of the new workbook, create two new modules [Functions, Procedures] and then just copy the code text across from one to the other.
3 In the VBEditor window of the old one, in the Microsoft Excel objects list select each sheet in turn {including ThisWorkbook} then right-click and select View code. If there is anything in any of them, copy it to the same place in the new workbook.



Denis

Just for the hell of it, run that UnhideSheets macro on another copy of that old one to see what it makes visible?

Denis