如何创建并将ActiveX Control commandbutton作为可变Excel VBA

Question

我正在尝试使用Excel VBA创建一个新的ActiveX ControlButton。我有一个过去有效的循环VBA，thefile1.1.xlsm具有工作簿的主列表。我需要在〜3200个工作簿中添加一个命令button，因此我将使用DO-LOOP宏。这是引用的循环代码。

Sub Macro2() 
Application.ScreenUpdating = False
Dim sFile As String
Dim wb As Workbook
Dim FileName1 As String
Dim FileName2 As String
Dim wksSource As Worksheet
Const scWkbSourceName As String = "theFILE 1.1.xlsm"
Set wkbSource = Workbooks(scWkbSourceName)
Set wksSource = wkbSource.Sheets("Sheet1") ' Replace 'Sheet1' w/ sheet name of SourceSheet
Const wsOriginalBook As String = "theFILE 1.1.xlsm"
Const sPath As String = "E:ExampleFolder"
SourceRow = 5
Do While Cells(SourceRow, "D").Value <> ""
    Sheets("Sheet1").Select
    FileName1 = wksSource.Range("A" & SourceRow).Value
    FileName2 = wksSource.Range("K" & SourceRow).Value
    sFile = sPath & FileName1 & "" & FileName2 & ".xlsm"
    Set wb = Workbooks.Open(sFile)
        ''insert code for loop operation
    '''CLOSE WORKBOOK W/O BEFORE SAVE
    Application.EnableEvents = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.EnableEvents = True
SourceRow = SourceRow + 1
Loop
End Sub

我想将按钮设置为变量（我认为），因此我可以编辑格式/属性，并希望以后在按钮中添加一个宏。

    Dim buttonControl As MSForms.CommandButton
    Set buttonControl = _
        ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Link:=False, _
        DisplayAsIcon:=False, _
        Left:=1464, Top:=310, Width:=107.25, Height:=30)
    With buttonControl.Opject
        .Caption = "OPEN FOLDER"
        .Name = "cmd_OPEN_FOLDER"
    End With

我有一个'运行时错误13：键入不匹配'错误。我不确定为什么，因为在正确的位置创建了" commandbutton1"。

Answer 1

OLEObjects.Add创建OLEObject并将其添加到OLEObjects集合中；Add函数返回的对象是OLEObject，而不是MSForm.CommandButton。这是OLEObject.Object的基本类型 - 因此，将buttonControl设置为返回对象的.Object属性：

Set buttonControl = _
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, _
    DisplayAsIcon:=False, _
    Left:=1464, Top:=310, Width:=107.25, Height:=30).Object

该按钮是在正确的位置中创建的，因为Add函数有效并返回 - 类型不匹配的失败是将返回的OLEObject分配到CommandButton变量之后，此操作后立即分配。

随后的With块可以是With buttonControl。