hI THERE!I'd like to ask help on how to delete row based on a value entered
in a textbox located in a user form.The userform and the worksheet where the
data to be deleted is stored are located in the same workbook. Example:
After a user input a four digit number it will automatically search in a
specific column of a particular sheet of a workbook. If a match was found the
data will be displayed in there respective control of the form and when an
OKcommand button is clicked the found data will be deleted. Pls.. Help!

This will work. It needs modified if the value could be found more
than once and you want to delete all instances.

Dim miRow As Long

Private Sub CommandButton1_Click()
If miRow = 0 Then Exit Sub
If Label2 = "Found!" Then
Sheets("Sheet1").Rows(miRow).Delete
Label2 = ""
End If
miRow = 0
End Sub

Private Sub TextBox1_AfterUpdate()
Dim iFind As Integer

iFind = TextBox1.Value
Do
miRow = miRow + 1
If iFind = Sheets("Sheet1").Range("A" & miRow) Then
Label2.Caption = "Found!"
Exit Sub
End If
Loop Until Sheets("Sheet1").Range("A" & iRow + 1) = ""
Label2 = "Not found."
End Sub

Hth,
Merjet

Public cell as Range

Private Sub TextBox1_Change()
Dim rng as Range, rng1 as Range
Dim s as String

if len(Textbox1.Value) = 4 then
if isnumeric(Textbox1.Value) then
set cell = Nothing
Textbox2.value = ""
Textbox3.Value = ""
s = Textbox1.Value
set rng = Worksheets("Data").Range("C:C")
Set rng1 = rng.Find(What:=s, _
After:=rng(rng.count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng1 is nothing then
set cell = rng1
Textbox2.value = cell.offset(0,-2)
Textbox3.Value = cell.offset(0,-1)
end if
end if
end if
End Sub


Private Sub CommandButton1_Click()
if not cell is nothing then
if len(trim(textbox1.value)) = 4 then
Textbox1.Value = ""
Textbox2.Value = ""
Textbox3.Value = ""
cell.EntireRow.Delete
set cell = Nothing
end if
end if
end Sub

--
Regards,
Tom Ogilvy

"Chin
x21" wrote:

hI THERE!I'd like to ask help on how to delete row based on a value entered
in a textbox located in a user form.The userform and the worksheet where the
data to be deleted is stored are located in the same workbook. Example:
After a user input a four digit number it will automatically search in a
specific column of a particular sheet of a workbook. If a match was found the
data will be displayed in there respective control of the form and when an
OKcommand button is clicked the found data will be deleted. Pls.. Help!

Thanks Tom! You got what I mean.. I've been waiting for your response cause
I find it easy to understood.
"Tom Ogilvy" wrote:

Public cell as Range

Private Sub TextBox1_Change()
Dim rng as Range, rng1 as Range
Dim s as String

if len(Textbox1.Value) = 4 then
if isnumeric(Textbox1.Value) then
set cell = Nothing
Textbox2.value = ""
Textbox3.Value = ""
s = Textbox1.Value
set rng = Worksheets("Data").Range("C:C")
Set rng1 = rng.Find(What:=s, _
After:=rng(rng.count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng1 is nothing then
set cell = rng1
Textbox2.value = cell.offset(0,-2)
Textbox3.Value = cell.offset(0,-1)
end if
end if
end if
End Sub


Private Sub CommandButton1_Click()
if not cell is nothing then
if len(trim(textbox1.value)) = 4 then
Textbox1.Value = ""
Textbox2.Value = ""
Textbox3.Value = ""
cell.EntireRow.Delete
set cell = Nothing
end if
end if
end Sub

--
Regards,
Tom Ogilvy

"Chin
x21" wrote:

hI THERE!I'd like to ask help on how to delete row based on a value entered
in a textbox located in a user form.The userform and the worksheet where the
data to be deleted is stored are located in the same workbook. Example:
After a user input a four digit number it will automatically search in a
specific column of a particular sheet of a workbook. If a match was found the
data will be displayed in there respective control of the form and when an
OKcommand button is clicked the found data will be deleted. Pls.. Help!