Macro to show message if any sheets are hidden

[dlz217]

Hi. I'm trying to just create a msgbox if there are any sheets hidden in the workbook. (This is part of a longer series of steps, so I want this prompt as a check.)

Thanks.

 

[RedBeard]

This should work:

Private Sub ReportVisibility()   'List very hidden sheets in Immediate window   
Dim ws As Worksheet   
For Each ws In ThisWorkbook.Worksheets     
	If ws.Visible = xlSheetVeryHidden Then       
		Debug.Print ws.Name & " is very hidden"     
	ElseIf ws.Visible = xlSheetHidden Then       
		Debug.Print ws.Name & " is hidden"     
	End If 
Next 
End Sub

 

[mumps]

Maybe:

Sub dlz217()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Visible = xlSheetVeryHidden Or ws.Visible = xlSheetHidden Then
            MsgBox (ws.Name & " is hidden.")
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[dlz217]

Got it. Thanks.

 

[My Aswer Is This]

Try this:

Sub check_If_Hidden()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
    For i = 1 To Sheets.Count
    If Sheets(i).Visible = False Then ans = ans & Sheets(i).Name & vbNewLine
    Next
    If ans <> "" Then MsgBox "These sheets are hidden" & vbNewLine & ans
    If ans = "" Then MsgBox "There are no hidden sheets"
    
Application.ScreenUpdating = True
End Sub