Wednesday, October 27, 2010

How do you create a delete button for a form with a macro in Excel?

I managed to create a form to add records to a worksheet. Now what I want to do is add a 'Delete Record' button to the form to delete the last entry (or any other entry for that matter). First, how do you identify the entry you wish to delete? My solution is to use the first column as a reference column. Try using a macro like this:

Private Sub CommandButton1_Click()

Dim c As Long

Dim iRecord As String

Dim Deleted As Boolean


 

iRecord = InputBox("Enter Record Number to Delete")

c = Cells(Rows.Count, 1).End(xlUp).Row

Deleted = False

While Not Deleted

If Cells(c, 1) = iRecord Then

Rows(c).Delete shift:=xlUp

Deleted = True

End If

c = c - 1

If c <= 0 Then Deleted = True

Wend

End Sub