Make a button delete the row on which it is

Question

I have made a to do list on Excel. I have a button that lets me add a task and some details about the task on a new row. Basically, I want to add an instruction that, for every new task added, also adds a button at the end of my row that deletes said row (to delete the task when it's over).

Now I have tried this among other things but encountered several problems :

Set btn = ActiveSheet.Buttons.Add(cellul.Offset(0, 1).Left, _
    cellul.Offset(0, 1).Top, cellul.Offset(0, 1).Width,_
    cellul.Offset(0, 1).Height)
With btn
    .OnAction = "deletebutton"
    .Caption = "Done !"
    .Name = "Deleteline"
End With

And

Sub deletebutton()
    Activecell.Entirerow.Delete
End Sub

Now that works in the sense that it does give me a button where I want, but as I later have to sort my tasks (by deadline), I am left with buttons that don't move with the sorting (or maybe the ActiveCell bit ruins it) and I am left with buttons not deleting the right task.

Does anyone know how I could refer to the row a button is in (that way I could code that in the button's macro), or any other way to make a button delete the row it's actually in?

Answer 1

You need to delete the row in relation to the position of the button that was clicked. Use the Application Caller method to get the name of the clicked button, and the TopLeftCell property to get its position.

Using .Name = "Deleteline" names all the buttons the same, which makes identifying the clicked button difficult. Unless you have a good reason for doing this, delete this line.

Refactored deletebutton sub (assuming .Name = "Deleteline" is removed)

Sub deletebutton()
    Dim rng As Range
    Dim rngOld As Range
    Dim msgRes As VbMsgBoxResult


    Set rngOld = ActiveCell
    Set rng = ActiveSheet.Buttons(Application.Caller).TopLeftCell.EntireRow

    rng.Select
    msgRes = MsgBox("Proceed?", vbOKCancel, "About to Delete this row.")
    If msgRes = vbOK Then
        rng.Delete
    End If
    rngOld.Select
End Sub