从Excel VBA控件内自引用 [英] Self-referencing from inside an Excel VBA control

问题描述

我试图从按钮的单击事件内部获取按钮控件的属性值，而不使用按钮的名称（因为我想为Excel工作表中的每个按钮使用相同的代码）。

I'm trying to get a property value of a button control from inside the button's click event without using the button's name (since I want to use the same code for each of many buttons on the Excel sheet).

经过多次研究，我看到很多参考文献，尝试以下内容：

After much research, I see many references to try the following:

Me.ActiveControl.name

或

Me.Shapes(Application.Caller).Name

然而，的那些在Excel中执行时抛出错误。请注意，我使用的是Excel 2010。

However, both of those throw an error when executed from within Excel. Note that I'm using Excel 2010.

感谢您提前提供任何帮助。
Lee

Thanks for any help in advance. Lee

推荐答案

你想要的是可能的，但是您需要创建一个类

What you want is possible but for that you need to create a Class

执行此操作。

插入一个类模块并粘贴此代码。

Insert a Class Module and paste this code there.

Option Explicit

Public WithEvents MyButton As MSForms.CommandButton

Private Sub MyButton_Click()
    MsgBox MyButton.Name
End Sub

下一步插入一个模块并放置此代码有

Next Insert a module and place this code there

Dim shpButtons() As New Class1

Sub StartCode()
    Dim shp As Shape
    Dim btnCount As Long

    ReDim shpButtons(1 To 1)

    btnCount = 0

    For Each shp In ActiveSheet.Shapes
        If shp.OLEFormat.Object.OLEType = xlOLEControl Then
            btnCount = btnCount + 1
            ReDim Preserve shpButtons(1 To btnCount)
            Set shpButtons(btnCount).MyButton = shp.OLEFormat.Object.Object
        End If
    Next
End Sub

Sub StopCode()
    Dim iBtn As Long

    On Error Resume Next
    For iBtn = LBound(shpButtons) To UBound(shpButtons)
        Set shpButtons(iBtn).TheText = Nothing
    Next
End Sub

现在只需运行 Sub StartCode（）

接下来，当您单击ActiveX CommandButton时，您将获得它的名称。

Next when you click the ActiveX CommandButton then you will get it's name.

这篇关于从Excel VBA控件内自引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！