MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA code to delete people from a list using the name in a textbox

  Asked By: Abbie    Date: Nov 28    Category: MS Office    Views: 2211

I am new to VBA (Excel) and I'm trying to create a userform to allow a
user to add/delete names to/from a list on another sheet. The name is
entered in a textbox, then the add or delete button is clicked. I have
the add button working. Can anyone help me with the code to find the
name and delete them from the list?



3 Answers Found

Answer #1    Answered By: Sophia Campbell     Answered On: Nov 28

I do this routinely by keeping track of the currentRow while using the
scroll buttons (Worksheet is protected)

I have some custom formatting that I like to keep on screen so if the
currentRow is the last row I simple clear contents instead of deleting
the row. Deleting removes the row and causes my number formatted rows to
decrease by one so sooner or later my screen formatting gets gobbled up.

Sub cmdDelete_Click()

If lCurrentRow = nLastRow Then
Cells(lCurrentRow, 2).EntireRow.ClearContents
Cells(lCurrentRow, 2).EntireRow.Delete
End If

nLastRow = Range("B65536").End(xlUp).Row


Remove focus from <DELEETE> to avoid enter Key


End Sub

Answer #2    Answered By: Andrew Brown     Answered On: Nov 28

Here is the code  I am currently using for the add  button, plus what
I have so far for the delete  button. I need to fill in the gap for
the find  the TextName.Value in the list  located on the "Resources"
worksheet and delete the name.

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Resources")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a name
If Trim(Me.TextName.Value) = "" Then
MsgBox "Please enter a name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextName.Value

'clear the data
Me.TextName.Value = ""

End Sub

Private Sub CmdDel_Click()
Dim smessage As String
Dim ws As Worksheet
Set ws = Worksheets("Resources")

smessage = "Are you sure you want to delete " + TextName.Text
+ "?"
If MsgBox(smessage, vbQuestion + vbYesNo, _
"Confirm Delete") = vbYes Then

End If

End Sub

Answer #3    Answered By: Gustavo Costa     Answered On: Nov 28

Looks like all you need is to use the Find method - something like this
(not verified). FIND is Very powerful - I usually use function that
receives the sheet  and string as parameters but it is not necessary.

With ws 'Your Worksheet where the row will be deleted

Set rFind = Columns(2).Find(What:= TextName.Text,
LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

delete  Selected ROW from Worksheet


end with