CODEWITHSUNDEEP
excelvba
bo-oz
bo-oz

Reputation: 2872

Dynamically added button, how to get some sort of value in the function

I dynamically add a button to each row I create in a table. Each button has a unique name and calls the same function BtnGoogle. This function should return a value in the same row as the button was placed in. Since the buttons are not actually part of the row, I need some way to transport the row number to the function, but I'm running out of ideas:

    Set btn = sheet.Buttons.Add(targetCell.Left, targetCell.Top, targetCell.Width, targetCell.RowHeight)
    With btn
      .OnAction = "BtnGoogle"
      .Caption = "Google Tag"
      .Name = "Btn" & CStr(lastRow(columns("Button1").Index).Address)
    End With

Sub BtnGoogle()
    MsgBox Application.Caller
End Sub

I'm able to show the button name in the messagebox this way, but I can't do anything with that Caller and get the row number I passed in from the string. Any ideas?

Upvotes: 0

Views: 51

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

Sub BtnGoogle()

    Dim addr As String

    addr = Replace(Application.Caller,"Btn","")

    MsgBox ActiveSheet.Range(addr).Value ' for example

End Sub

Upvotes: 1

Related Questions