Userform Search & present in listbox then delete selected item

[andycgreen]

Hi,

I am using Excel 2010, i have a userform "Remove Site Issues" which search text is entered in textbox and results are displayed in listbox, a result can then be selected and row deleted which references worksheet "Site Issues"

My problem is that the userform all looks like it works ok but when i search for data it will look like it searches correctly but when i delete Test1 it will sometimes delete a Test2 row data ???
-----------------------------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton2_Click()
'Delets Data from "Site Issues" Database
If ListBox1.ListIndex >= 0 Then

cevap = MsgBox("Are you sure?", vbYesNo)

If cevap = vbYes Then


Silinecek_Satir = ListBox1.ListIndex + 2
Sheets("Site Issues").Rows(Silinecek_Satir).Delete
ListBox1.Clear

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""



End If
End If
cmdsearch_Click
End Sub

Private Sub CommandButton3_Click()
'Clears all fields data
Dim ctl
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ListBox" Then
ctl.Value = ""
ListBox1.Clear
End If
Next ctl

End Sub


Private Sub CommandButton4_Click()
Unload Me
End Sub

Private Sub Label1_Click()

End Sub

Private Sub ListBox1_Click()
Bulunan_Satir_No = ListBox1.ListIndex + 2
'Displays ListBox Data into TextBox fields
TextBox1 = Sheets("Site Issues").Range("A" & Bulunan_Satir_No).Value

TextBox2 = Sheets("Site Issues").Range("B" & Bulunan_Satir_No).Value

TextBox3 = Sheets("Site Issues").Range("C" & Bulunan_Satir_No).Value

TextBox4 = Sheets("Site Issues").Range("D" & Bulunan_Satir_No).Value

TextBox5 = Sheets("Site Issues").Range("E" & Bulunan_Satir_No).Value

TextBox6 = Sheets("Site Issues").Range("F" & Bulunan_Satir_No).Value

TextBox7 = Sheets("Site Issues").Range("G" & Bulunan_Satir_No).Value

If noclick = True Then Exit Sub
With Me
.TextBox1 = ListBox1.Column(0)
.TextBox2 = ListBox1.Column(1)
.TextBox3 = ListBox1.Column(2)
.TextBox4 = ListBox1.Column(3)
.TextBox5 = ListBox1.Column(4)
.TextBox6 = ListBox1.Column(5)
.TextBox7 = ListBox1.Column(6)

End With


End Sub

Private Sub cmdsearch_Click()

Dim cell As Range
Dim sAddr As String
Dim Sh As Worksheet
Set Sh = ActiveWorkbook.Sheets("Site Issues")

ListBox1.Clear

Set cell = Sh.Range("A2:A2010").Find( _
What:=txtsearch.Text, _
After:=Range("A2010"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
sAddr = cell.Address
Do
With ListBox1
.AddItem cell.Offset(0, 0).Value
.List(.ListCount - 1, 0) = cell.Value
.List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
.List(.ListCount - 1, 3) = cell.Offset(0, 3).Value
.List(.ListCount - 1, 4) = cell.Offset(0, 4).Value
.List(.ListCount - 1, 5) = cell.Offset(0, 5).Value
.List(.ListCount - 1, 6) = cell.Offset(0, 6).Value

End With
Set cell = Sh.Range("A2:A2010").FindNext(cell)
Loop While cell.Address <> sAddr
End If


End Sub




Private Sub UserForm_Initialize()
If Worksheets("Site Issues").FilterMode Then
Worksheets("Site Issues").ShowAllData
End If
End Sub
----------------------------------------------------------------------------------------------------------------------------------------------

 

[Richard U]

It's in your find logic.

change LookAt:=xlPart to LookAt:=xlWhole

 

[andycgreen]

Hi,

Thanks for your quick reply, i have tried this but still get the same issue