VBA Button position not updating-CodePudding

i have this code to create a button and assign a macro to it


    Private Sub createButton_line(ByVal name As String, ByVal position, ByVal line As Integer)

    Dim btn As Button
    Dim R As Range
    Set R = ActiveSheet.Range(position)
    Set btn = ActiveSheet.Buttons.Add(R.Left, R.Top, R.Width, R.Height)
        With btn
            .Caption = name
            .Placement = xlMove
            .name = name
            .OnAction = "'test """ & btn.TopLeftCell.Address & """'"
    
        End With
    End Sub

Public Sub test(ByVal p As Variant)
    Range("A22").value = p
End Sub

But if i insert a row with another function or manually the test function always show (write in a cell) me the same Address i tried btn.TopLeftCell.Row or btn.TopLeftCell.Adress but it's always showing me the same address which is the range that was used to create the button.

I did try many solutions found on the forum and on stack overflow post but it still not working I expect that I get the actual position of my button when I insert a row ahead.

CodePudding user response：

OnAction is static - so when you pass the Address as parameter it won't change.

Therefore you need to retrieve the button itself in your test-sub - and check the position there. For that: pass the name of the button to 'OnAction' instead - and retrieve the address when called by test

Public Sub createButton_line(ByVal name As String, ByVal position, ByVal line As Integer)

Dim btn As Button
Dim R As Range
Set R = ActiveSheet.Range(position)
Set btn = ActiveSheet.Buttons.Add(R.Left, R.Top, R.Width, R.Height)
    With btn
        .Caption = name
        .Placement = xlMove
        .name = name
        .OnAction = "'test """ & name & """'"
    End With
End Sub



Public Sub test(ByVal NameOfButton As Variant)
    Dim btn As Button
    Set btn = ActiveSheet.Buttons(NameOfButton)
    Range("A22").Value = btn.TopLeftCell.Address
End Sub