Indicating worksheets are hidden

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

Robert Christie

Hi everyone
I have a one or two workbooks that have hidden sheets.
Can anyone help with a macro to indicate via a msgbox
that the workbook just opened contains hidden sheets.?
Is it possible to list the hidden sheet (Tab) names in
the msgbox.?

I tried to modify code I found on this site, which
informed the user, the sheet tab they had just click on
contained hidden rows

My code so far does not work, the msgbox shows up whether
sheets are hidden or not.

Private Sub Workbook_Open()
'
On Error GoTo EndMacro:
If Me.ActiveWorkbook.Worksheets.Count <> _
Me.ActiveWorkbook.Worksheets
(xlHidden).ActiveWorkbook.Worksheets.Count Then
MsgBox "This Workbook has Hidden Worksheets."
End If
EndMacro:
'
End Sub

TIA

Regards Bob C
 
Try this

For Each sh In ThisWorkbook.Sheets
If sh.Visible = True Then hidcount = hidcount + 1
Next
AllSheets = ThisWorkbook.Sheets.Count
MsgBox "There are " & AllSheets & " sheets but " & hidcount & " are hidden"
 
Robert,

Here is a slightly different approach...

'-------------------------
Sub FindHiddenSheets()
Dim objSht As Object

For Each objSht In Sheets
If Not objSht.Visible Then
Application.CommandBars.FindControl _
(ID:=30006).Controls("Sheet").Controls("Unhide...").Execute
Exit For
End If
Next 'Sht

Set objSht = Nothing
End Sub
'-------------------------

'Of course one could just go to Format | Sheet | Unhide and see if the menu item
is enabled.

Regards,
Jim Cone
San Francisco, CA
 
Hi Ron

Had to re-word the MsgBox " are hidden to " visible"
As test of code on total 3 sheets with 2 hidden = 1 is
indicating open sheets.

Thankyou

Bob C.
-----Original Message-----
Try this

For Each sh In ThisWorkbook.Sheets
If sh.Visible = True Then hidcount = hidcount + 1
Next
AllSheets = ThisWorkbook.Sheets.Count
MsgBox "There are " & AllSheets & " sheets but " & hidcount & " are hidden"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Robert Christie" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
Hi Jim
Thankyou for your reply, works great, it's just a
reminder at the start that 2 or 3 sheets of 20 are
hidden, if you need to open one it's there at the start.

Regards
Bob C.
 
Back
Top