Hey Matt,

This seems to work for me to go through all open workbooks. This will take into account any hidden sheets e.g. Personal.xls ...

Sub ShowAllModules()
    Application.ScreenUpdating = False
    Dim N As Integer, i As Long, T As Integer
    Dim VBC As VBComponent, sTmp As String, wb As Workbook
    Dim myBook As Workbook, mySheet As Worksheet, currBook As Workbook
    Set currBook = ActiveWorkbook
    If ActiveWorkbook Is Nothing Then
        Workbooks.Add
        Set myBook = ActiveWorkbook
        Set mySheet = myBook.ActiveSheet
    Else
        Sheets.Add after:=Sheets(Sheets.Count)
        Set myBook = ActiveWorkbook
        Set mySheet = myBook.ActiveSheet
    End If
    mySheet.Range("A1") = "COMPONENT NAME"
    mySheet.Range("B1") = "COMPONENT TYPE"
    mySheet.Range("C1") = "BOOK NAME"
    mySheet.Range("D1") = "PROCEDURES"
    N = 2
    For Each wb In oExcel.Workbooks
        wb.Activate
        For Each VBC In ActiveWorkbook.VBProject.VBComponents
            mySheet.Range("A" & N) = VBC.name
            T = VBC.Type
            If T = 1 Then mySheet.Range("B" & N) = "Basic Module"
            If T = 2 Then mySheet.Range("B" & N) = "Class Module"
            If T = 3 Then mySheet.Range("B" & N) = "UserForm"
            If T = 11 Then mySheet.Range("B" & N) = "ActiveX"
            If T = 100 Then mySheet.Range("B" & N) = "Book/Sheet Class Module"
            mySheet.Range("C" & N) = ActiveWorkbook.name
            With VBC.CodeModule
                i = .CountOfDeclarationLines + 1
                sTmp = ""
                Do Until i >= .CountOfLines
                    mySheet.Range("D" & N) = .ProcOfLine(i, vbext_pk_Proc)
                    i = i + .ProcCountLines(.ProcOfLine(i, vbext_pk_Proc), vbext_pk_Proc)
                    If i < .CountOfLines Then N = N + 1
                Loop
            End With
            N = N + 1
        Next
    Next wb
    mySheet.Columns.AutoFit
    currBook.Activate
    Application.ScreenUpdating = True
End Sub

You know what would be really cool? Is to show (on top of something like this) all subs/procedures in all installed add-ins also. Now THAT would be something useful.