vba button - find which was clicked

Question

I have assigned macro to few buttons.

How can I find out inside macro which button was clicked?

I am doing as user form, where he can input peoples from family:

name1:
surname1:

name2:
surname2: |add next member|

I wish button to appear always in last row of the last added person. For simplicity I think it is better to have like 100 empty forms in the sheet but all invisible at the begining.
Then when user clicks add next member I simply make next rows visible, and move button to next person. But to do that I need to know my current position.

Similar with deletion I would make rows invisible when remove button is clicked.

name1:
surname1: [remove]

name2:
surname2: [remove]

name3:
surname3: |add next member|

I need to know which remove button was clicked.

EDIT: Found in web - what do you think, seems to be best /way

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select

Answer 1

I always write wrappers for each button that then call the macro in question.

Like so:

Public Sub StoreButton_Click()

  Call StoreTransValues(ActiveSheet)

End Sub

If you have only one button for any one page, you can just get the ActiveSheet property, and it will be the button on that page.

---

Edit:

Here's the code to get and use the name of the calling button:

Dim ButtonText As String

ButtonText = Application.Caller

ActiveSheet.Shapes(ButtonText).Delete

You would use the .Move method to move the button.

Answer 2

I finally found the solution for determining which button in a Worksheet was pushed. Credit is due to Derk at http://www.ozgrid.com/forum/showthread.php?t=33351.

My final example code:

Sub HereIAm()
    Dim b As Object
    Dim cs, rs As Integer
    Dim ss, ssv As String
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        rs = .Row
        cs = .Column
    End With
    ss = Left(Cells(1, cs).Address(False, False), 1 - (ColNumber > 26)) & rs
    ssv = Range(ss).Value
    MsgBox "Row Number " & rs & "    Column Number " & cs & vbNewLine & _
        "Cell " & ss & "   Content " & ssv
End Sub

If you don't need the cells label, Cells(rs,cs).Value works as well.

Answer 3

Dim button as a string:

    button = ActiveSheet.Shapes(Application.Caller).Name

Answer 4

Since you have a macro wired to your button(s), I assume you know which button it is that was clicked. To get the location of the button, use this:

ActiveSheet.Shapes("ButtonName").TopLeftCell.Address

To move a button to a new location, use this:

Dim NewAddress as Range
NewAddress = ActiveSheet.Cells(5, 5) 'Or where ever you need it to go
ActiveSheet.Shapes("ButtonName").Left = NewAddress.Left
ActiveSheet.Shapes("ButtonName").Top = NewAddress.Top