如何使用 vba 创建 ActiveX 按钮并向其添加代码（告诉它要运行的子程序）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23299301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create an ActiveX button and add code to it (tell it what sub to run) using vba?
提问by mountainclimber
I'd like to create an ActiveX button in a sheet and assign a code to it (i.e. tell it to what sub to run and that sub already exists).
我想在工作表中创建一个 ActiveX 按钮并为其分配一个代码（即告诉它要运行哪个子程序并且该子程序已经存在）。
I can create the button: (recorded)
我可以创建按钮：（录制）
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=41395.5882352941, Top:=234.705882352941, _
Width:=119.117647058824, Height:=39.7058823529412).Select
But I'd like to change the caption and assign a sub to it so when someone clicks the newly created activex button it runs a sub that already lives in a module within the workbook that the button is to be created. Everything happens and lives in ThisWorkbook.
但是我想更改标题并为其分配一个子项，以便当有人单击新创建的 activex 按钮时，它会运行一个子项，该子项已经存在于要创建该按钮的工作簿中的模块中。一切都发生在本工作簿中。
Thanks!
谢谢！
PS - I cannot use a Command button....I can only use ActiveX
PS - 我不能使用命令按钮......我只能使用 ActiveX
回答by mountainclimber
This works:
这有效：
Sub AddingButtons()
Dim btn As Button
Dim t As Range
Dim Obj As Object
Dim Code As String
Dim ShtNm As String
With ThisWorkbook
.Worksheets.Add(After:=Worksheets(1)).Name = "My New Worksheet"
.Sheets("My New Worksheet").Activate
End With
ShtNm = ActiveSheet.Name
Sheets(ShtNm).Select
Set t = ActiveSheet.Range("D3:F4")
'create button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=t.Left, Top:=t.Top, Width:=t.Width, Height:=t.Height)
'button text
ActiveSheet.OLEObjects(1).Object.Caption = "Show Data Selection Window"
'macro text
Code = "Private Sub CommandButton1_Click()" & vbCrLf
Code = Code & "Call MyTestSub(ShtNm)" & vbCrLf
Code = Code & "End Sub"
MsgBox "Worksheet name is " & ActiveSheet.Name
'add macro at the end of the sheet module
With ActiveWorkbook.VBProject.VBComponents(Worksheets(ShtNm).CodeName).CodeModule
.insertlines .CountOfLines + 1, Code
End With
End Sub
改编自：http: //social.msdn.microsoft.com/Forums/office/en-US/1a48cdfd-42af-486c-a4a5-3d5d5797d00c/adding-an-active-x-command-button-and-its-code -using-in-excel-vba?forum=exceldev
PS - Tim - had the answer in his comment on my original question. Thx tim. How to create a dynamic button in excel
PS - 蒂姆 - 在他对我原来问题的评论中得到了答案。谢谢蒂姆。 如何在excel中创建动态按钮
回答by StandardDeviation
For Excel 2013 click Developer/Insert/ActiveX Command Button. After insertion, right click the button, choose properties for caption and other manipulations, choose view code for macro assignment.
对于 Excel 2013，单击开发人员/插入/ActiveX 命令按钮。插入后，右键单击按钮，选择标题和其他操作的属性，选择宏分配的查看代码。
