Userform to look up data, and delete if necessary

[ExtraCheese]

I have created a database creation tool, where the user can add rows of data using an userform.

When the user wants to look up the entered data, he can click on another userform. This all works perfectly.

However, I would like to add the option for the user to also delete the data using the same userform as he used to find the data.
I have tried adding another button in the userform interface, but that didn't really work.

The user can enter the reference in 'Referentie', then click the 'Zoeken' button. Code below for this button.

Private Sub CommandButton1_Click()

Dim id As String, rowcount As Long, foundcell As Range
Dim n As Long
id = TextBox3.Value

If id <> "" Then
With Worksheets("Data")
'ID is in col D
Set foundcell = .Columns("D").Find(What:=id, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not foundcell Is Nothing Then
n = foundcell.Row
TextBox1.Value = .Cells(n, 1)
TextBox2.Value = .Cells(n, 2)
TextBox4.Value = .Cells(n, 3)
TextBox5.Value = .Cells(n, 7)
TextBox6.Value = .Cells(n, 8)
TextBox7.Value = .Cells(n, 5)
TextBox8.Value = .Cells(n, 6)
CommandButton1.Enabled = False
Else
TextBox1.Value = ""
TextBox2.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
MsgBox "Partijnummer niet gevonden"
End If

End With
End If

 

[MikeVol]

Hello @ExtraCheese.
Try next code (untested)

Option Explicit

' Delete Button
Private Sub CommandButton3_Click()
    Dim id          As String
    id = TextBox3.Value

    If id <> "" Then
        Dim ws      As Worksheet
        Set ws = ThisWorkbook.Worksheets("Data")

        Dim foundcell As Range
        Set foundcell = ws.Columns("D").Find(What:=id, LookIn:=xlValues, lookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
        If Not foundcell Is Nothing Then
            Dim n   As Long
            n = foundcell.Row

            ws.Range("A" & n & ":H" & n).Delete Shift:=xlUp

            TextBox1.Value = ""
            TextBox2.Value = ""
            TextBox4.Value = ""
            TextBox5.Value = ""
            TextBox6.Value = ""
            TextBox7.Value = ""
            TextBox8.Value = ""
            TextBox3.Value = ""

            MsgBox "Data removed! ", vbInformation
        Else
            MsgBox "Id not found", vbExclamation
        End If

        Set foundcell = Nothing
        Set ws = Nothing
    Else
        MsgBox "Enter ID to delete! ", vbExclamation
    End If

End Sub

I hope I helped you. Good luck.

 

[DanteAmor]

However, I would like to add the option for the user to also delete the data using the same userform as he used to find the data.

Try this:

Private Sub CommandButton2_Click()
  Dim foundcell As Range
  
  With TextBox3
    If .Value = "" Then
      MsgBox "Enter ID"
      .SetFocus
      Exit Sub
    End If
    
    'ID is in col D
    Set foundcell = Sheets("Data").Range("D:D").Find(.Value, , xlValues, xlWhole, , , False)
    If Not foundcell Is Nothing Then
      foundcell.EntireRow.Delete
      MsgBox "ID deleted"
    Else
      MsgBox "ID does not exists"
    End If
  End With
End Sub

 

[ExtraCheese]

Try this:

Private Sub CommandButton2_Click()
  Dim foundcell As Range
 
  With TextBox3
    If .Value = "" Then
      MsgBox "Enter ID"
      .SetFocus
      Exit Sub
    End If
   
    'ID is in col D
    Set foundcell = Sheets("Data").Range("D:D").Find(.Value, , xlValues, xlWhole, , , False)
    If Not foundcell Is Nothing Then
      foundcell.EntireRow.Delete
      MsgBox "ID deleted"
    Else
      MsgBox "ID does not exists"
    End If
  End With
End Sub

Thanks! This worked like a charm!

 

[MikeVol]

@ExtraCheese,
What about the code from post #2, is it not working? Clarify this!

 

[dmt32]

Hi

Just an observation, as your search code has already found the record & populated the controls, you should be able avoid need to repeat the use Range.Find function in the delete the code with just a simple modification to your code.

Try the following & see if will work for you

In your CommandButton1 code MOVE the following object variable the very TOP of your userforms code page placing it OUTSIDE any procedure

Dim foundcell As Range

Replace your CommandButton code (Delete button) with following

Private Sub CommandButton2_Click()

Dim i As Long

'delete record
If Not foundcell Is Nothing Then foundcell.EntireRow.Delete: Set foundcell = Nothing

'clear controls
For i = 1 To 8
  Me.Controls("TextBox" & i).Value = ""
Next i

End Sub

Note - no error handling has been included

Hope Helpful



Dave