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.

5
  • 1
    buttonControl.Opject - Opject? That looks like a typo to me - but I don't think you even need Object in there either. Commented Dec 19, 2018 at 17:20
  • @dwirony I'm at a loss. What are you recommending? Commented Dec 19, 2018 at 17:22
  • 1
    Is that not a typo of Object? Commented Dec 19, 2018 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) Commented Dec 19, 2018 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. Commented Dec 19, 2018 at 17:27

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

2 Comments

Ahh. So then would his next line still just be With buttonControl?
Ah I almost had it!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.