4

I am trying to Create a New ActiveX Control CommandButton with Excel VBA. I have a loop VBA which has worked in the past, theFile1.1.xlsm has the master list of the workbooks. I need to add a CommandButton to ~3200 workbooks, so I will be using the Do-Loop macro. Here is the Loop code for reference.

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

I would like to have the button set as a Variable (i think), so I can edit the formatting/properties and hopefully add a macro to the button later.

    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

I have a 'Run-time error 13: Type Mismatch' error. I am unsure why, because a 'CommandButton1' is created in the correct place.

braX
  • 11,506
  • 5
  • 20
  • 33
Kenny
  • 343
  • 2
  • 9
  • 28
  • 1
    `buttonControl.Opject` - Opject? That looks like a typo to me - but I don't think you even need `Object` in there either. – dwirony Dec 19 '18 at 17:20
  • @dwirony I'm at a loss. What are you recommending? – Kenny Dec 19 '18 at 17:22
  • 1
    Is that not a typo of `Object`? – dwirony Dec 19 '18 at 17:23
  • Oh! You're correct but that doesn't seam to be the problem. the problem seams to be on or just after this line Set buttonControl = _ ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=1464, Top:=310, Width:=107.25, Height:=30) – Kenny Dec 19 '18 at 17:26
  • What line is highlighted when the error occurs? And like I said, my guess is that you don't need `.Object` in there - just `With buttonControl`. `.Caption` is a property of `CommandButton`. – dwirony Dec 19 '18 at 17:27

1 Answers1

4

OLEObjects.Add creates an OLEObject and adds it to the OLEObjects collection; the object returned by the Add function is OLEObject, not MSForm.CommandButton. That's the underlying type of OLEObject.Object - so, set your buttonControl to the .Object property of the returned object:

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

The button is created in the correct place, because the Add function works and returns - what's failing with a type mismatch is the assignment of the returned OLEObject into a CommandButton variable, immediately after that operation.

The subsequent With block can then be just With buttonControl.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235