Creating Search and Delete Button to delete record

[Mnet22]

Dear Excel users, I have created a userform with textboxes that Adds a staff members first name, last name and address on sheet 3 called "Lists". It is working perfectly - How can I also search and delete the same records. Any Suggestions?? All help will be greatly appreciated!!

Private Sub btn_Add_Click()

Dim X As Integer
Dim nextrow As Range
Dim cNum As Integer
Dim ws As Worksheet
Dim ans As Long


On Error GoTo errHandler:

'find the next row
 Set nextrow = Sheet3.Cells(rows.Count, 3).End(xlUp).Offset(1, 0)
 
     Application.ScreenUpdating = False
  
 
    'End If
  If Me.Reg1.Value = "" Or Me.Reg2.Value = "" Or Me.Reg3.Value = "" Then
        MsgBox "There is insufficient data. Madatory fields must be added (*)", vbExclamation, "Mandatory fields are incomplete"
        Exit Sub
    End If
    
  cNum = 3
 For X = 1 To 3
    If Me.Controls("Reg" & X).Value = "" Then
    MsgBox "You must add all data"
    Exit Sub
  End If
  Next
  
  For X = 1 To cNum
    nextrow = Me.Controls("Reg" & X).Value
    Set nextrow = nextrow.Offset(0, 1)
    Next
    'clear
  For X = 1 To cNum
    Me.Controls("Reg" & X).Value = ""
    Next

   On Error GoTo 0
   Exit Sub
   
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"

       
  With ws

           
        nextrow.Offset(0, 3).Value = Me.Reg1
        nextrow.Offset(0, 4).Value = Me.Reg2
        nextrow.Offset(0, 5).Value = Me.Reg3
       
        End With
        
        


     'sort the data

    Sheet3.Select

    With ws

        ws.Range("C3:E10000").Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess

    End With

    'return to sheet

    Sheet3.Select
    
    'reset the form
    Unload Me
    AddDB1_UF.Show
    'update the sheet
    Application.ScreenUpdating = True
    
'clear the values
With Me
.Reg1.Value = ""
.Reg2.Value = ""
.Reg3.Value = ""

End With

MsgBox "The data has been sent to the database"

End Sub

 

[Mnet22]

Due to NO Response or any suggestions I have also posted this question on Oz Grid - https://www.ozgrid.com/forum/forum/...ing-search-and-delete-button-to-delete-record

 

[Mnet22]

Due to NO Response or any suggestions I have also posted this question on Oz Grid - https://www.ozgrid.com/forum/forum/...ing-search-and-delete-button-to-delete-record

and https://www.excelforum.com/editpost.php?do=updatepost&postid=4784505

 

[Mnet22]

Dear Excel users in the meantime I have created the following script - There is a combobox called cmbshno that calls up the three Items - Textbox 1 - Lastname, Textbox2 - Firstname and Tetbox3 - Address - But I still have problems deleting and updating - there seems to be a problem with the

rowselect = Me.cmbshno.Value

- Any Suggestions

Private Sub cmbshno_Change()

If Me.cmbshno.Value = "" Then

MsgBox "SL No Can Not be Blank!!!", vbExclamation, "SL No"

Exit Sub

End If

SLNo = Me.cmbshno.Value

On Error Resume Next

Me.TextBox1.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("Lists").Range("C3:E1000"), 1, 0)
Me.TextBox2.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("Lists").Range("C3:E1000"), 2, 0)
Me.TextBox3.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("Lists").Range("C3:E1000"), 3, 0)


End Sub

Private Sub cmdupdate_Click()
If Me.cmbshno.Value = "" Then

MsgBox "SL No Can Not be Blank!!!", vbExclamation, "SL No"

Exit Sub

End If

SLNo = Me.cmbshno.Value

Sheets("Lists").Select

Dim rowselect As Double

'rowselect = Me.cmbshno.Value

rowselect = rowselect + 1

rows(rowselect).Select

Cells(rowselect, 3) = Me.TextBox1.Value

Cells(rowselect, 4) = Me.TextBox2.Value

Cells(rowselect, 5) = Me.TextBox3.Value


rowselect = rowselect - 1

msg = "Sl No " & rowselect & "  Successfully Updated...Continue?"

Unload Me

ans = MsgBox(msg, vbYesNo, "Update")

If ans = vbYes Then

UserForm1.Show

Else

Sheets("Lists").Select
End If
End Sub

Private Sub CommandButton1_Click()

Sheets("Lists").Select

Dim msg As String

Dim ans As String

If Me.cmbshno.Value = "" Then

MsgBox "Sl No can not be Blank!!", vbExclamation, "SL No"

Exit Sub

End If

Dim rowselect As Double

'rowselect = Me.cmbshno.Value

rowselect = rowselect + 1

rows(rowselect).EntireRow.Delete

rowselect = rowselect - 1

msg = "Sl No " & rowselect & "  Successfilly Deleted...Continue?"

Unload Me

ans = MsgBox(msg, vbYesNo, "Delete")

If ans = vbYes Then

UserForm1.Show

Else

Sheets("Lists").Select

End If




End Sub

Private Sub UserForm_Click()

End Sub

 

[Mnet22]

SOLVED it Myself - Thank you!!

 

[si3po]

SOLVED it Myself - Thank you!!

Apologies for dragging up a really ancient thread, but I'm now using a very similar version of your original code and am also getting the "Type Mismatch" runtime error. Can I ask what you did or how you solved the issue?