VBA Excel 2010-如何将宏分配给使用宏创建的命令按钮 [英] VBA Excel 2010 - How to assign a macro to a command button that was made with a macro

问题描述

我有一个创建命令按钮的宏，但是我无法为VBA中的按钮分配任何宏

I have a macro that creates a command button however I am unable to assign any macro to the button in the VBA

已经查看了此链接，但该链接适用于用户表单(但我不够好，无法根据我的需要进行更改)

have looked at this link but its for a userform (but I'm not good enough to be able to change it to suit what I need)

下面是我当前正在处理的代码，我想我需要在 With 语句中添加一些内容，但我不知道它会是什么

The code I am currently tring is below, I'm guessing I need to add something to the With Statement but I dont know what it would be

Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Range("I3") 'just an example - you get that from your own script
    MyR_T = MyR.Top         'capture positions
    MyR_L = MyR.Left        '...
    'create button
    Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False)

    'set main button properties
    With MyB
        .Name = "MyPrecodedButton"          'important - code must exist ... see below
        .Object.Caption = "Load UseForm"
        .Top = MyR_T
        .Left = MyR_L
        .Width = 130
        .Height = 30
        .Placement = xlMoveAndSize
        .PrintObject = True                 'or false as per your taste
    End With

推荐答案

使用.onAction方法

Use .onAction method

类似这样的东西

Sheets("someVeryFunnySheetName").buttons("someSeriousButtonName").onAction = "macroName"

这里是一个示例，如果您想将参数传递给该宏(我认为axleOutputSHeetCounter是一些整数)

Here is one example, if you wana to pass parameter to that macro (axleOutputSHeetCounter is some integer i think)

With chartSheet.Buttons("closeOutputSheet")
    .OnAction = "'Module7_Axle.closeOutputSheet """ & axleOutputSheetCounter & """'"
    .Characters.text = "Delete sheet"
    .Characters.Font.Size = 16
End With

用于activeX按钮

edit: for activeX buttons here you can find question with same issue and working solution

这篇关于VBA Excel 2010-如何将宏分配给使用宏创建的命令按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！