我们如何知道VBA中按钮的发送者？

问题描述

美好的一天
我想找出许多按钮的发件人。

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);
}

我没有任何想法在VBA（而不是VB！）。 VBA意味着应用程序的Visual Basic 。在VBA中有可能吗？我正在使用Excel。我尝试过 Application.Caller ，但它不起作用。任何样本 - 建议？

I don't have any idea in VBA (not VB!). VBA means Visual Basic for Applications. It is possible in VBA? I'm using Excel. I've tried Application.Caller, but it doesn't work. Any samples - suggestions?

推荐答案

您必须连接按钮事件。您可以在工作表激活事件处理程序中执行此操作：

You have to wire up the button events. You can do so in the Worksheet Activate event handler:

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

然后使用以下代码创建一个名为ButtonEvents的类模块：

Then make a Class Module named ButtonEvents with this code:

Public WithEvents cmdButton As MSForms.CommandButton

Private Sub cmdButton_Click()
 MsgBox cmdButton.Caption & " was pressed!"
End Sub

这篇关于我们如何知道VBA中按钮的发送者？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！