Excel VBA - Get corresponding Range for Button int

问题:

How can I get the row the clicked button?

Hello everyone i need some help over here ;)

I'm a newbie in VBA I am trying to make a button in Excel but I am stumped on how to figure out the row of the clicked button. Like in the photo I want that when I click the button "Sélectionner un poste" then he will tell me his position (The button which i clicked in which row)

Here is my code to create the button:

Sub AjouterBoutonPoste(positionX As Integer, positionY As Integer, nom As String)
   Set t = ActiveSheet.Range(Cells(positionX, positionY), Cells(positionX, positionY))
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .OnAction = "PosteBtAction"
     .Caption = "Sélectionner un poste"
     .Name = nom & CStr(positionX) & CStr(positionY)

   End With
End Sub

Here is my code for the event button:

Sub PosteBtAction()
   AssocierSessoinCandidature.Show
End Sub

I have a application window which name is AssocierSessoinCandidature. Actually I want to get the position which i clicked and send this information to the application window.

Here is my example Excel sheet:

回答1:

Call the below Sub when the button is clicked

Sub foo()

Dim obj As Object
Dim row_no As Integer

Set obj = ActiveSheet.Buttons(Application.Caller)
With obj.TopLeftCell
    row_no = .Row
End With
MsgBox "The Button is in the row number " & row_no

End Sub

回答2:

I have used something very similar, you can adapt the below code:

Sub Button24_Click()
Dim strShape As String
strShape = Shapes("button24").TopLeftCell.Address
CallAnotherFunction strShape
End Sub

Upon clicking the button, this code will take the range of button24 as a string (not sure why I didn't use a range, you could if you wish) and pass it to the function CallAnotherFunction

In summary, this is what you need:

Shapes("button24").TopLeftCell.Address

or

Shapes("button24").TopLeftCell.Row

回答3:

You can access properties of the Button object for TopLeftCell and BottomRightCell to get the range addresses that bound the control:

Option Explicit

Sub Test()

    Dim ws As Worksheet
    Dim btn As Object
    Dim strAddressTopLeft As String
    Dim strAddressBottomRight As String
    Dim strButtonName As String

    Set ws = ActiveSheet

    For Each btn In ws.Buttons
        strAddressTopLeft = btn.TopLeftCell.Address
        strAddressBottomRight = btn.BottomRightCell.Address
        strButtonName = btn.Name
        Debug.Print "Range of button (" & strButtonName & "): " & _
            strAddressTopLeft & ":" & _
            strAddressBottomRight
    Next btn

End Sub