CODEWITHSUNDEEP
vbaexcelmacros
Jokru
Jokru

Reputation: 69

Use button caption as variable excel

This has to be an obvious thing to do, but I want to use the name of a command button that is pressed in excel as a variable in a macro. The macro would be as simple as setting the value of a cell to the name of the button; So if button captioned "10" is pressed the cells value would be "10", the same macro needs to work for all numeral button captions/names. Again, sorry if this is obvious!

Upvotes: 0

Views: 2207

Answers (1)

S Meaden
S Meaden

Reputation: 8270

Try this, works with Forms buttons but not ActiveX buttons.

Sub Button1_Click()

    If Not IsError(Application.Caller) Then

        Dim obj2 As Object
        Set obj2 = ActiveSheet.Shapes.Item(Application.Caller)
        Debug.Print obj2.AlternativeText
    End If
End Sub

but your question asked about command buttons (the ActiveX variety) and this is more involved, we need to find the shape and then drill in via OLEFormat and two layers of IDispatch to get a reference to the command button, then we use WithEvents to fire event handler.

Option Explicit

'* Inside Sheet module

Private WithEvents mcmd As MSForms.CommandButton

Private Sub Initialise()
    Dim obj As Object
    Set obj = Me.Shapes.Item("CommandButton1")
    Set mcmd = obj.OLEFormat.Object.Object

End Sub

Private Sub mcmd_Click()

    Debug.Print mcmd.Caption

End Sub

Sadly you need to initialise code like this for every command button I think.

Upvotes: 1

Related Questions