Assinging a macro to a Commandbutton variable with Excel VBA

Question

so I've created a Macro which writes a new Macro to Sheet1 of the VBA editor, then creates the ActiveX Control CommandButton. Now I need the button to run the newly created macro when clicked upon. The Button has been created as a Object Variable called 'buttonControl'.

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule
    With CodeMod
        .InsertLines 34, "Private Sub cmd_OPEN_FOLDER_Click()"
        .InsertLines 35, "    Dim FolderPath As String"
        .InsertLines 36, "    Dim FinalFolder As String"
        .InsertLines 37, "        FolderPath = ""C:\ExampleFolder1\ExampleFolder2\"""
        .InsertLines 38, "        FinalFolder = ActiveSheet.Range(""N1"").Value & ""\"""
        .InsertLines 39, "    Call Shell(""explorer.exe """""" & FolderPath & FinalFolder & """", vbNormalFocus)"
        .InsertLines 40, "End Sub"

    End With

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).Object

    With buttonControl
        .Caption = "OPEN FOLDER"
        .Name = "cmd_OPEN_FOLDER"
        .BackColor = "12713921"
        Selection.OnAction = "cmd_OPEN_FOLDER_Click()" 'assigns the macro

    End With

I now have a 'Run-time error 438: Object doesn't support this property or method' on

        Selection.OnAction = "cmd_OPEN_FOLDER_Click()" 'assigns the macro

When I end the VBA from the Dialog Box and click on the newly button it seamed to have associated correctly. How does one do this without the error message?

Excel doesn't allow creation of ActiveX control (on a sheet) and assignment in the same cycle. use aaplication.ontime, it will work. — cyboashu
– cyboashu, Commented Dec 20, 2018 at 19:20
Don't work off Selection. You have a buttonControl, just set its OnAction property. You're getting error 438 because Selection isn't the button (and there's no need to select it anyway). Or... waaaaait a minute - isn't OnAction for Forms Controls, not ActiveX? Why not use any shape then? I often use a rounded-corner rectangle shape in place of a button. Looks much prettier! — Mathieu Guindon
– Mathieu Guindon, Commented Dec 20, 2018 at 19:28
@MathieuGuindon is there no way to do this with a ActiveX button? I do have other ActiveX CommandButtons on this sheet and I'd like to keep the button formatting the same. — Kenny
– Kenny, Commented Dec 20, 2018 at 19:43
An ActiveX command button, once added to a worksheet, becomes an event provider on that worksheet. Hence you don't need to "wire up" what happens on click, because to "wire up" an event handler in VBA all you need is a correctly-named procedure with the correct signature. So, if the button is named MyButton, you need to have a MyButton_Click() procedure in that worksheet's code-behind, and it should "just work". — Mathieu Guindon
– Mathieu Guindon, Commented Dec 20, 2018 at 19:46
I would absolutely avoid using any underscores in the name of the button. — Mathieu Guindon
– Mathieu Guindon, Commented Dec 20, 2018 at 19:47

Tim Williams · Accepted Answer · 2018-12-20 19:45:41Z

This worked fine for me. OnAction isn't for ActiveX buttons - you name the sub to match the button name plus "_Click"

Sub tester()

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Sheet1")
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        .InsertLines 34, "Private Sub cmd_OPEN_FOLDER_Click()"
        .InsertLines 34, "Msgbox ""OK"""
        .InsertLines 40, "End Sub"
    End With

    Dim buttonControl 'As MSForms.CommandButton

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

    buttonControl.Name = "cmd_OPEN_FOLDER"
    With buttonControl.Object
        .Caption = "OPEN FOLDER"
        .BackColor = 12713921
    End With
End Sub

Collectives™ on Stack Overflow

Assinging a macro to a Commandbutton variable with Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related