Q:

Self-referencing from inside an Excel VBA control

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

or
Me.Shapes(Application.Caller).Name

However, both of those throw an error when executed from within Excel.  Note that I'm using Excel 2010.
Thanks for any help in advance.
Lee

A:

What you want is possible but for that you need to create a Class
Do this.
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

Now simply run the Sub StartCode()
Next when you click the ActiveX CommandButton then you will get it's name.
 

