Textbox on a form deletes rows

[osevero]

Hello everyone!

I need help. I intend to use a form to delete a row from the number that is written in the textbox. You can see here the example: 10806

I wrote this code but it isn't well developed.


Private Sub CommandButton1_Click()

ThisWorkbook.Worksheets("Sheet1").Activate

Range("B8").Select
While ActiveCell <> ""
If TextBox1.Text = ActiveCell Then
Reply = MsgBox(" Number found. Are you sure you want to delete?", 3, " Delete ")
While ActiveCell = TextBox1.Text
ActiveCell.EntireRow.Delete

Wend

End If

ActiveCell.Offset(1, 0).Activate

Wend

With Sheets("Sheet1")
Range("B7:B8").Value = WorksheetFunction.Transpose(Array(1, 2))
Range("B7:B8").AutoFill Destination:=.Range("B7:B" & .Range("C" & .Rows.Count).End(xlUp).Row), Type:=xlFillDefault

End With

End Sub


What I need:

-The code has to search in the ID column, the number that is written in the textbox and delete that row.

-If the number is found then a message should appear: "ID found, are you sure you want to delete?" Option: Yes or no. If the answer is "Yes" then the row is deleted. If the answer is "No", then the message box disappears.

-If not found, the ID column, the number entered in the textbox, then a message appears: "The number was not found." Option: Ok

[osevero]

The form:

10807

[Ringhal]

Sub FindID()
Dim WHAT_TO_FIND As String
Dim ws As Excel.Worksheet
Dim FoundCell As Excel.Range

WHAT_TO_FIND = InputBox("ID", "Delete")

Set ws = ActiveSheet
Set FoundCell = ws.Range("B:B").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)
If Not FoundCell Is Nothing And FoundCell <> 0 Then
If MsgBox("ID found, are you sure you want to delete?", vbYesNo) = vbYes Then
FoundCell.EntireRow.Delete
Else
Exit Sub
End If
Else
MsgBox (WHAT_TO_FIND & "The number was not found.")
End If

End Sub

[osevero]

Thanks Ringhal for the reply :) That code has a problem, it opens a window to write the ID number and I do not want it because I have created a form with a textbox to write the number. In other words I want a code for when I click "Confirm" on my form (see the photo in my secound post)

[Ringhal]

OK, the attached file didn't have a form, so try this, edit as needed:


Private Sub CommandButton1_Click()
Dim WHAT_TO_FIND As String
Dim ws As Excel.Worksheet
Dim FoundCell As Excel.Range

WHAT_TO_FIND = UserForm1.TextBox1.Value

Set ws = ActiveSheet
Set FoundCell = ws.Range("B:B").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)
If Not FoundCell Is Nothing Then
If MsgBox("ID found, are you sure you want to delete?", vbYesNo) = vbYes Then
FoundCell.EntireRow.Delete
Else
Unload Me
Exit Sub
End If
Else
MsgBox (WHAT_TO_FIND & "The number was not found.")
End If
Unload Me
End Sub

[osevero]

Works very well, thanks :) Is it possible to delete at once multiple rows? For example, I would like to delete 4 numbers of ID (4 rows) writing the 4 numbers in the textbox. Do you know how to do?

[Ringhal]

You can remove the "Unload me" line (both lines) from the code and you will be able to delete the IDs one at a time. Otherwise, if you want to delete multiple rows at once, you can create extra textboxes and duplicate part of the code. I'm not sure how you could do it from a single textbox.

[osevero]

Ok thanks!! :) Does anyone know how I can to delete multiple rows at once from a single textbox? : pray2: