Option Explicit
Public Sub Demo()
Dim wks As Excel.Worksheet
Dim colCBO As VBA.Collection
Dim item As Object
Dim i As Long
Set colCBO = New VBA.Collection
Set wks = Worksheets("Tabelle1")
For Each item In wks.Shapes
If item.Type = MsoShapeType.msoFormControl Then
If item.FormControlType = XlFormControl.xlDropDown Then
Call colCBO.Add(item)
End If
End If
Next
Debug.Print vbNewLine; "[Formularsteuerlemente:" & colCBO.Count & "]"
For Each item In colCBO
Debug.Print Spc(1); "Name: '"; item.Name & "'"
Next
Call MsgBox("'" & wks.Name & "' hat " & colCBO.Count & " Formularsteuerelemente vom Typ 'Kombinationfeld'.", vbInformation)
Set colCBO = New VBA.Collection
Set wks = Worksheets("Tabelle1")
For Each item In wks.OLEObjects
If item.OLEType = XlOLEType.xlOLEControl Then
If 0 = StrComp(item.progID, "FORMS.COMBOBOX.1", vbTextCompare) Then
Call colCBO.Add(item)
End If
End If
Next
Debug.Print "[ActiveX-Steuerlemente:" & colCBO.Count & "]"
For Each item In colCBO
Debug.Print Spc(1); "Name: '"; item.Name & "'"
Next
Call MsgBox("'" & wks.Name & "' hat " & colCBO.Count & " ActiveX-Steuerelemente vom Typ 'Kombinationfeld'.", vbInformation)
End Sub