List All Objects whole WorkBook

[marreco]

Hi.
I want list all controls in whole workbook.
This code bellow made by Ron de broin, but only list in active tab.

Sub ListAllObjectsActiveSheet()
'Fonte:http://www.rondebruin.nl/win/s4/win002.htm
    Dim NewSheet As Worksheet
    Dim MySheet As Worksheet
    Dim myshape As Shape
    Dim I As Long

    Set MySheet = ActiveSheet
    Set NewSheet = Worksheets.Add

    With NewSheet
        .Range("A1").Value = "Name"
        .Range("B1").Value = "Visible(-1) or Not Visible(0)"
        .Range("C1").Value = "Shape type"
        I = 2
       
        For Each myshape In MySheet.Shapes
            .Cells(I, 1).Value = myshape.Name
            .Cells(I, 2).Value = myshape.Visible
            .Cells(I, 3).Value = myshape.Type
            I = I + 1
        Next myshape
       
        .Range("A1:C1").Font.Bold = True
        .Columns.AutoFit
        .Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
    End With

End Sub

 

[Chihiro]

Something like this.

Sub ListAllObjectsActiveSheet()
'Fonte:http://www.rondebruin.nl/win/s4/win002.htm
  Dim NewSheet As Worksheet
    Dim MySheet As Worksheet
    Dim myshape As Shape
    Dim I As Long
   
    Set NewSheet = Worksheets.Add

    With NewSheet
        .Range("A1").Value = "Name"
        .Range("B1").Value = "Visible(-1) or Not Visible(0)"
        .Range("C1").Value = "Shape type"
        .Range("D1").Value = "Sheet Name"
        I = 2
        For Each MySheet In ThisWorkbook.Worksheets
            For Each myshape In MySheet.Shapes
                .Cells(I, 1).Value = myshape.Name
                .Cells(I, 2).Value = myshape.Visible
                .Cells(I, 3).Value = myshape.Type
                .Cells(I, 4).Value = myshape.Parent.Name
                I = I + 1
            Next myshape
        Next MySheet
     
        .Range("A1:D1").Font.Bold = True
        .Columns.AutoFit
        .Range("A1:D" & Rows.Count).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
    End With

End Sub

 

[marreco]

Very nice, thank you very much!!