vba 在运行时向工作表添加命令按钮并定义事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8250050/
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
Adding command buttons to worksheet at run time and also define events
提问by Ank
I am trying to add an ActiveX command button in my work sheet at run time. The number of command buttons will depend on the number of lines in the work sheet. I plan to give x and y coordinates in the command button property to position them correctly. I understand that we can insert command buttons in user form this way.
我试图在运行时在我的工作表中添加一个 ActiveX 命令按钮。命令按钮的数量取决于工作表中的行数。我计划在命令按钮属性中给出 x 和 y 坐标以正确定位它们。我知道我们可以通过这种方式在用户表单中插入命令按钮。
Private Sub CommandButton1_Click()
Me.Controls.Add _
"Forms.CommandButton.2", "CopyOf"
End Sub
How can we insert command buttons in a work sheet (not user form). Worksheets("abc").Add doesn't work. Also how to I define independent click events for them. For example if I click a command button, it should tell me which row is it in.
我们如何在工作表（不是用户表单）中插入命令按钮。工作表（“abc”）。添加不起作用。还有如何为他们定义独立的点击事件。例如，如果我单击一个命令按钮，它应该告诉我它在哪一行。
Thanks
谢谢
--------------UPDATE -----------------------------
- - - - - - - 更新 - - - - - - - - - - - - - - -
So I am able to add the command buttons in their required spot dynamically based on the number of lines in my worksheet.
因此，我可以根据工作表中的行数在所需位置动态添加命令按钮。
Private Sub addb3(ByVal rows_present_alerts As Integer)
Dim topcounter As Double
topcounter = 15.75
For i = 2 To rows_present_alerts ' The first row has the column headers
With Worksheets("abc").OLEObjects
.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=509.25, Top:=topcounter, Width:=48, Height:=14.25 _
).Select
End With
topcounter = topcounter + 15 ' (not sure this approach will work in monitors with diff screen resolution but anyways)
Next i
End Sub
I want to assign click events to each command button. When I click the command button, it should tell me which row is it in.
我想为每个命令按钮分配点击事件。当我单击命令按钮时，它应该告诉我它在哪一行。
采纳答案by Jon Egerton
The easiest way to work this sort of thing out is to record a macro, then perform the action, and see what code gets recorded. In this case I recorded a macro and added a button to the sheet and got the code snippet:
解决此类问题的最简单方法是记录一个宏，然后执行操作，然后查看记录了哪些代码。在这种情况下，我录制了一个宏并在工作表中添加了一个按钮并获得了代码片段：
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveSheet.Buttons.Add(126.75, 39.75, 46.5, 19.5).Select
End Sub
You should be able to take it from there...
你应该可以从那里拿走它......
回答by HRgiger
it can help, probably you will wonder also how to access it;
它可以提供帮助，可能您还会想知道如何访问它；
Sub addButton()
Dim myButton As OLEObject
Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=0, Top:=300, Height:=20, Width:=200)
myButton.Placement = XlPlacement.xlFreeFloating
myButton.Object.Caption = "Click Me..."
myButton.Name = "DynamicButton"
End Sub
Private Sub DynamicButton_Click()
MsgBox "Hello sheet"
End Sub
