如何获得CommandButton集合？

Question

我需要列出工作表上大量旧的ActiveX命令按钮的标题。

我该如何参考收藏呢？

PseudoCode：

For each btn in Activesheet.CommandButtons 
     Debug.Print btn.Caption
Next btn

Answer 1

Dim s As Worksheet

Set s = ActiveSheet

Dim o As OLEObject

For Each o In s.OLEObjects

    If TypeName(o.Object) = "CommandButton" Then
        Debug.Print o.Object.Caption
    End If

Next

Answer 2

更新了我从MrExcel网站找到的代码：

Dim BtnActX As Integer
Dim MyShapes As OLEObjects
Dim Btn As OLEObject

'OLE Programmatic Identifiers for Commandbuttons = Forms.CommandButton.1
Set MyShapes = ActiveSheet.OLEObjects
For Each Btn In MyShapes
If Btn.progID = "Forms.CommandButton.1" Then
BtnActX = BtnActX + 1
abc = Btn.Object.Caption
MsgBox "command button text is: " & abc
End If
Next

Answer 3

在工作表中循环遍历OLEObjects，您可以使用它们的progID并看到它等于"Forms.CommandButton.1“。

注意事项：请不要使用ActiveSheet，而是使用完全限定的对象，比如Worksheets("Sheet1")。

Option Explicit

Sub FindCommandButtonsInOLEObjects()

Dim Sht                 As Worksheet
Dim Obj                 As OLEObject

Set Sht = ThisWorkbook.Sheets("Sheet1") '<-- modify "Sheet1" to your sheet's name

' loop thourgh all OLE objects in "Sheet1"
For Each Obj In Sht.OLEObjects
    If Obj.progID Like "Forms.CommandButton.1" Then ' <-- check if current object is type Comman Button
        Debug.Print Obj.Object.Caption
    End If
Next Obj 

End Sub