我们如何知道 VBA 中按钮的发送者？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10130300/
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 do we know the sender of the buttons in VBA?
提问by RePRO
I would like to find out the senderof many buttons.
我想找出发件人的许多按钮。
Something like that in C#:
在C# 中是这样的：
Button b = new Button();
b.Text = "Click 1";
b.Location = new Point(20, 20);
b.Size = new Size(100, 20);
// Our Handler
b.Click += new EventHandler(myClick);
// Implementation of next button...
// Add to form
this.Controls.Add(b);
this.Controls.Add(nextButton);
...
// And our event
private void myClick(object sender, System.EventArgs e)
{
var mysender = ((Button)sender).Name;
doAnything(mysender);
}
It is possible in VBA (Visual Basic for Applications)? I'm using Excel. I tried Application.Caller.
在 VBA (Visual Basic for Applications) 中是可能的吗？我正在使用 Excel。我试过Application.Caller。
回答by N0Alias
You have to wire up the button events. You can do so in the Worksheet Activate event handler:
您必须连接按钮事件。您可以在 Worksheet Activate 事件处理程序中执行此操作：
Dim arrEvents As Collection
Private Sub Worksheet_Activate()
Dim objButtonEvents As ButtonEvents
Dim shp As Shape
Set arrEvents = New Collection
For Each shpCursor In Me.Shapes
If shpCursor.Type = msoOLEControlObject Then
If TypeOf shpCursor.OLEFormat.Object.Object Is MSForms.CommandButton Then
Set objButtonEvents = New ButtonEvents
Set objButtonEvents.cmdButton = shpCursor.OLEFormat.Object.Object
arrEvents.Add objButtonEvents
End If
End If
Next
End Sub
Then make a Class Module named ButtonEvents with this code:
然后使用以下代码创建一个名为 ButtonEvents 的类模块：
Public WithEvents cmdButton As MSForms.CommandButton
Private Sub cmdButton_Click()
MsgBox cmdButton.Caption & " was pressed!"
End Sub
回答by airman000616
Rename your Shapes.Name values to something you can use however you need.
将您的 Shapes.Name 值重命名为您可以根据需要使用的值。
When you create a group of shapes in Excel, lets say rectangles, right click the first object then go to the Formulas tab and click "Define Name". At the bottom of the pup-up window will be a line: Refers to [ ="Rectangle 73" ] This is your VBA Shapes.Name value. (Doing anything here will not change the VBA Shapes.Name value)
当您在 Excel 中创建一组形状时，比如说矩形，右键单击第一个对象，然后转到“公式”选项卡并单击“定义名称”。在小窗口的底部会有一行： 指的是 [ ="Rectangle 73" ] 这是你的 VBA Shapes.Name 值。（在这里做任何事情都不会改变 VBA Shapes.Name 值）
You can use the individual object name with IF Application.Caller.Name = "Rectangle 73"or you can rename your shaped in VBA to something more useful.
您可以使用单个对象名称，IF Application.Caller.Name = "Rectangle 73"也可以将 VBA 中的形状重命名为更有用的名称。
If you manually created each shape, they will be "Rectangle 73", "Rectangle 74", "Rectangle 75"... If you copied and pasted, they will be "Rectangle 73", "Rectangle 102", "Rectangle 103"... Now that we have our Shapes.Name values we can redefine them as needed in VBA.
如果您手动创建每个形状，它们将是“矩形 73”、“矩形 74”、“矩形 75”... 如果您复制粘贴，它们将是“矩形 73”、“矩形 102”、“矩形 103” ... 现在我们有了 Shapes.Name 值，我们可以根据需要在 VBA 中重新定义它们。
Sub nameShapes()
Dim shp As String
Dim shpType As String
Dim myName As String
'original root name of group shapes *do not include trailing space*
shpType = "Rectangle"
'new name of group shapes
myName = "newName"
'first number value of new shape names
n = 1
'number values of first, second, and last shapes
n1 = 73
n2 = 103
nx = 124
'active code --------------------------------------
'--------------------------------------------------
shp = shpType & " " & n1
ActiveSheet.Shapes(shp).Name = myName & " " & n
For x = n2 To nx
n = n + 1
shp = shpType & " " & x
ActiveSheet.Shapes(shp).Name = myName & " " & n
Next n
'--------------------------------------------------
'active code --------------------------------------
End Sub
Place this code in its own separate module, then simply enter the values and click the play button in the VBA window toolbar for RunSub.
将此代码放在其自己单独的模块中，然后只需输入值并单击 RunSub 的 VBA 窗口工具栏中的播放按钮。
Now, you can use n = Right(Application.Caller, 2)to get the number value of the button clicked. Be sure to define Dim n As Integer.
现在，您可以使用n = Right(Application.Caller, 2)来获取单击的按钮的数值。请务必定义Dim n As Integer.
If different button groups call the same function, you can use Select Case Left(Application.Caller, 7)to get the first 7 letters of the shape.name and Case "newName"for actions specific to that button group.
如果不同的按钮组调用相同的函数，您可以使用Select Case Left(Application.Caller, 7)获取 shape.name 的前 7 个字母以及Case "newName"特定于该按钮组的操作。
