Indicating worksheets are hidden
 

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

Indicating worksheets are hidden
 

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)
www.rondebruin.nl



"Robert Christie" wrote in message ...
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

Indicating worksheets 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

"Robert Christie" wrote in message
...
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

Indicating worksheets are hidden
 

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)
www.rondebruin.nl



"Robert Christie"
wrote in message news:14a4901c3f929$18390030
...
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



.

Indicating worksheets are hidden
 

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.

-----Original Message-----
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

"Robert Christie"
wrote in message
...
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



.

Indicating worksheets are hidden
 

True must be False in the code

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Robert Christie" wrote in message ...
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)
www.rondebruin.nl



"Robert Christie"
wrote in message news:14a4901c3f929$18390030
...
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



.