Go through all the objects (except the table) of a

Question

I would like to write a VBA program that parses all the elements of a worksheet except the 2-dimensional data table.

That means, I want to go through all the charts, buttons, checkboxs, etc. if they exist. I would like this list to be exhaustive. So the 1st question is what collections to go through.

One option is ActiveSheet.Shapes, another option is ActiveSheet.OLEObjects, I don't know if there is anything else... Could anyone tell me which one covers everything?

Once an item is identified, I want to determine if it is a chart, button, checkbox... I realize that the following code doesn't achieve it: it prints always Shape.

For Each sShape In ActiveSheet.Shapes
    MsgBox TypeName(sShape)
Next

Does anyone know how to show the type of an item?

Answer 1

As follow up from MSDN (Shape and OLEObject):

OLEObject Object:

Represents an ActiveX control or a linked or embedded OLE object on a worksheet.

Shape:

Represents an object in the drawing layer, such as an AutoShape, freeform, OLE object, or picture.

So, ActiveSheet.Shapes include ActiveSheet.OLEObjects.

Both collections doesn't include Tables, which are part of ListObjects collection.

So, you want Shapes collection. Following code prints types of all shapes in active sheet:

Sub test()
    Dim s As Shape

    For Each s In ActiveSheet.Shapes
        Debug.Print "Type: " & s.Type & "  Name: " & s.Name
        If s.Type = msoOLEControlObject Then
            Debug.Print "     OleObject Type: " & TypeName(s.OLEFormat.Object.Object)
        ElseIf s.Type = msoFormControl Then
            Debug.Print "     FormControl Type: " & s.FormControlType
        End If
    Next
End Sub

Possible output of subroutine:

Type: 3  Name: Chart 1
Type: 1  Name: Rectangle 2
Type: 12 Name: CheckBox1
      OleObject Type: CheckBox
Type: 8  Name: Button 2
      FormControl Type: 0

Notes:

1. s.Type represents type of Shape object and returns MsoShapeType Enumeration
2. if s.Type = msoOLEControlObject (OleObject), part TypeName(s.OLEFormat.Object.Object) returns name of OleObject type, like "CheckBox", "Button" and etc.
3. if s.Type = msoFormControl (Form control), part s.FormControlType returns XlFormControl Enumeration