时间戳

首页 > 解决方案 > 使用 Excel VBA 将宏分配给 Commandbutton 变量

excel - 使用 Excel VBA 将宏分配给 Commandbutton 变量

问题描述

所以我创建了一个宏，它将一个新宏写入 VBA 编辑器的 Sheet1，然后创建 ActiveX 控件命令按钮。现在我需要单击该按钮来运行新创建的宏。Button 已创建为名为“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

我现在有一个“运行时错误 438：对象不支持此属性或方法”

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

当我从对话框中结束 VBA 并单击它正确关联的新按钮时。如何在没有错误消息的情况下做到这一点？

标签： excelvbaactivexactivexobject

解决方案

这对我来说很好。 OnAction不适用于 ActiveX 按钮 - 您将 sub 命名为与按钮名称相匹配的名称加上“_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

推荐阅读