如何创建一个ActiveX按钮并添加代码(告诉它什么子运行)使用vba? [英] How to create an ActiveX button and add code to it (tell it what sub to run) using vba?
问题描述
我想在工作表中创建一个ActiveX按钮,并为其分配一个代码(即告诉它要运行什么子,那个子已经存在)。
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).
我可以创建按钮:(记录)
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
但是我想更改标题并分配一个子所以当有人点击新创建的activex按钮时,它运行一个已经存在于工作簿中的模块的子项,该子模块将被创建。一切都发生,并且生活在ThisWorkbook。
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.
谢谢!
PS - 我不能使用命令按钮... 。我只能使用ActiveX
PS - I cannot use a Command button....I can only use ActiveX
推荐答案
这样做:
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
PS - 蒂姆 - 在他对我原来的问题的评论中有答案。 Thx时间
如何在excel中创建动态按钮
PS - Tim - had the answer in his comment on my original question. Thx tim. How to create a dynamic button in excel
这篇关于如何创建一个ActiveX按钮并添加代码(告诉它什么子运行)使用vba?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!