Count of Visible Workbooks

Two functions that return a count of all workbooks with at least one visible window.

Function VisibleWbCount() As Long

    ‘Returns a count of all workbooks with at least
    ‘one visible window.  Takes no arguments.
    Dim oWb As Workbook
    Dim oWin As Window
    Dim lVisible As Long
    ‘Loop through workbooks
    For Each oWb In Application.Workbooks
        ‘Loop through windows for each wb
        For Each oWin In oWb.Windows
            ‘Increment counter and stop processing
            ‘windows for this workbook
            If oWin.Visible Then
                lVisible = lVisible + 1
                Exit For
            End If
        Next oWin
    Next oWb
    VisibleWbCount = lVisible
End Function

Function VisibleWbCount2()

    Dim oWin As Window
    Dim cBooks As Collection
    ‘Create collection to store unique
    ‘workbook names
    Set cBooks = New Collection

    ‘Loop though windows and add workbook name to
    For Each oWin In Application.Windows
        On Error Resume Next
            cBooks.Add oWin.Parent.Name, CStr(oWin.Parent.Name)
        On Error GoTo 0
    Next oWin
    VisibleWbCount2 = cBooks.Count
End Function

Posted in Uncategorized

2 thoughts on “Count of Visible Workbooks

  1. What about this, in the Active workbook modual?

    Sub bob()

    For Each sheet In Sheets
    If sheet.Visible = False Then j = j + 1
    Next sheet

    x = ActiveWorkbook.Sheets.Count

    VisSheets = x – j

    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.