VBA Count visible sheets (or hidden sheets)

[Derek]

Hi Guys

Need to count how many sheets are visible and how many are hidden.

Have tried - Thisworkbook.sheets.count - but that give them all
Have tried - Thisworkbook.sheets.visible.count - but Excel 2010 doesn't seem to like that.

Please can someone give me the code for this

Many thanks

Derek

 

[Glory]

for each sheet in thisworkbook.sheets

if sheet.visible = true then i = i + 1

next

msgbox i

 

[Derek]

Thanks Glory - much appreciated

Cheers

Derek

 

[Weaver]

try:

Sub vhCount()
    Dim v
    For Each s In ActiveWorkbook.Sheets
        If s.Visible Then v = v + 1
    Next s
    MsgBox "Visible : " & v & vbCrLf & _
        "Hidden : " & ActiveWorkbook.Sheets.Count - v
End Sub

PS, re Glory's code, I'd recommend avoiding variable names that could be mistaken for or even are reserved words, like 'sheet'

 

[Glory]

>_> It was just an example.

 

[Weaver]

>_> It was just an example.

Sure, but folks tend to take anything posted as code literally, especially if they're not used to vb.

 

[Derek]

Thanks Weaver, appreciated

Regards

Derek

 

[CatalinBogdan]

I added some improvements to the piece of code from above, to take into account all type of sheets (also those in xlVeryHidden status):
--------------------------------
Sub SheetCounting()
Dim v, h, vh, t As Integer
'count the total number of sheets:
t = ActiveWorkbook.Sheets.Count
'count the number of sheets in status "xlVeryHidden"
vh = 0
For Each s In ActiveWorkbook.Sheets
If (s.Visible = xlVeryHidden) Then vh = vh + 1
Next s
'count the number of sheets in status "xlHidden"
h = 0
For Each s In ActiveWorkbook.Sheets
If (s.Visible = xlHidden) Then h = h + 1
Next s
'count the number of sheets in status "xlVisible"
v = t - h - vh
msgbox "xlVisible = " & v & vbCrLf & "xlHidden = " & h & vbCrLf & "xlVeryHidden = " & vh & vbCrLf & "xlTotal = " & t
End Sub

 

[CatalinBogdan]

additional, even simpler way:
-------------------------------

Function SheetCount(intCommand As Integer) 'possible values for the command string: xlSheetHidden, xlSheetVeryHidden, xlSheetVisible or 9
Dim v As Integer
'count the total number of sheets:
If intCommand = 9 Then
SheetCount = ActiveWorkbook.Sheets.Count
Else
v = 0
For Each s In ActiveWorkbook.Sheets
If (s.Visible = intCommand) Then v = v + 1
Next s
SheetCount = v
End If
End Function

Sub Test_SheetCount()
msgbox "visible pages = " & SheetCount(xlSheetVisible)
msgbox "hidden pages = " & SheetCount(xlSheetHidden)
msgbox "very hidden pages = " & SheetCount(xlSheetVeryHidden)
msgbox "total pages = " & SheetCount(9)
End Sub