Put a button in a cell

[Christopher Benson-Manica]

I need a way to put a button in a specific cell (not a button that
floats) that deletes the row that it is on when pressed, from VBA
(since I need to do this iteratively). Is there a way to do it?
Thanks...

 

[Michael Malinsky]

Put the button where you want it then, while in design mode, right-click the
button and select Format Control. On the Properties tab, select "Don't move
or size with cells" to cause to button to remain in position regardless of
the adding or deleting of cells.

HTH
Mike.

 

[Christopher Benson-Manica]

Put the button where you want it then, while in design mode, right-click the
button and select Format Control. On the Properties tab, select "Don't move
or size with cells" to cause to button to remain in position regardless of
the adding or deleting of cells.

Hm... I guess my original post wasn't really clear. I need a button
(or something else!) that fits inside a cell completely and deletes
the row it's on (as well as itself) when clicked. It needs to be
inside a cell, because I want one of these for each row on a
worksheet, including rows not currently visible... I appreciate your
help, and I hope I'm being more clear this time.

 

[Michael Malinsky]

I feel I should respond so you don't think I'm ignoring you, but I don't
think I have an answer for you. My only thought would be to ask why not add
the "Delete Row" button on the toolbar?

 

[Christopher Benson-Manica]

I feel I should respond so you don't think I'm ignoring you, but I don't
think I have an answer for you. My only thought would be to ask why not add
the "Delete Row" button on the toolbar?

Well, I'd actually like to call a sub that does some other stuff in
addition to deleting the row... I got the impression that I wouldn't
be able to do what I wanted, so I put one button in that deletes rows
depending on what's in them instead. But thanks for your help - I
didn't know the button you spoke of existed

 

[Dave Peterson]

Try this against a copy of your worksheet--just in case:

Option Explicit
Sub AddButtons()

Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myBTN As Button

Set wks = ActiveSheet
With wks
.Buttons.Delete 'remove existing buttons???
Set myRng = .Range("a1:a10")
For Each myCell In myRng.Cells
With myCell
Set myBTN = .Parent.Buttons.Add _
(Height:=.Height, _
Width:=.Width, _
Left:=.Left, _
Top:=.Top)
myBTN.Name = "BTN_" & .Address(0, 0)
myBTN.OnAction = ThisWorkbook.Name & "!myBTNmacro"
myBTN.Caption = "Click Me"
End With
Next myCell
End With
End Sub
Sub myBTNMacro()
Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons(Application.Caller)
With myBTN
.TopLeftCell.EntireRow.Delete
.Delete
End With

End Sub