在运行时将命令按钮添加到工作表并定义事件 [英] Adding command buttons to worksheet at run time and also define events

问题描述

我正在尝试在运行时在工作表中添加ActiveX命令按钮。命令按钮的数量将取决于工作表中的行数。我计划在命令按钮属性中给出x和y坐标以正确定位它们。我了解我们可以通过用户形式插入命令按钮。

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.

Private Sub CommandButton1_Click()

Me.Controls.Add _
"Forms.CommandButton.2", "CopyOf"
End Sub

如何在以下位置插入命令按钮工作表（不是用户表单）。工作表（ abc）。添加无效。还有如何为它们定义独立的单击事件。例如，如果我单击命令按钮，它应该告诉我它在哪一行。

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.

谢谢

--------------更新-----------------------------

--------------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.

推荐答案

最简单的方法是要做的是记录一个宏，然后执行操作，然后查看记录了什么代码。在这种情况下，我记录了一个宏，并在工作表上添加了一个按钮，并获得了代码片段：

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...

这篇关于在运行时将命令按钮添加到工作表并定义事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！