Multiple lines with the same data? How do i stop this from happening?

[jayjay2022]

Hello eveyrone,

Everytime timw I use the search textbox and don't have caps on, the listbox shows multiple lines with the same data. My code does use the Ucase command but I would like to use upper or lower case with no duplicates

How do i stop this from happening?

My code

-----------------------------------------------
Private Sub textBox10_Change()

Dim sh As Worksheet
Set sh = Sheets("Daily Jobs")
Dim i As Long
Dim X As Long
Dim p As Long
Me.ListBox10.Clear

Me.TextBox10 = Format(StrConv(Me.TextBox10, vbUpperCase))

For i = 2 To sh.Range("B" & Rows.Count).End(xlUp).Row
For X = 1 To Len(sh.Cells(i, 2))
p = Me.TextBox10.TextLength

If UCase(Mid(sh.Cells(i, 2), X, p)) = Me.TextBox10 And Me.TextBox10 <> "" Then

With Me.ListBox10
.AddItem sh.Cells(i, 2)
.List(ListBox10.ListCount - 1, 0) = sh.Cells(i, 1)
.List(ListBox10.ListCount - 1, 1) = sh.Cells(i, 2)
.List(ListBox10.ListCount - 1, 2) = sh.Cells(i, 3)
.List(ListBox10.ListCount - 1, 3) = sh.Cells(i, 4)
.List(ListBox10.ListCount - 1, 4) = sh.Cells(i, 6)
.List(ListBox10.ListCount - 1, 5) = sh.Cells(i, 5)
.List(ListBox10.ListCount - 1, 6) = sh.Cells(i, 7)
.List(ListBox10.ListCount - 1, 7) = sh.Cells(i, 8)

End With
End If

Next X
Next i

End Sub
------------------------------------------------

Please screen shot attached

Thanks in advance.

 

[EXCEL MAX]

Hello Jayjay2022, welcome.
It's hard to explain why this happens.
I can only say textbox "Change" event is little bit tricky.
Try in this way and I hope you will see the difference.

Private Sub textBox10_Change()

    Dim sh As Worksheet
    Set sh = Sheets("Daily Jobs")
    Dim i As Long
    Dim X As Long
    Dim p As Long
    Me.ListBox10.Clear

    a = sh.Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To a
        For X = 1 To Len(sh.Cells(i, 2))
            p = Me.TextBox10.TextLength
            If UCase(Mid(sh.Cells(i, 2), X, p)) = Me.TextBox10 And Me.TextBox10 <> "" Then
                With Me.ListBox10
                    .AddItem sh.Cells(i, 2)
                    .List(ListBox10.ListCount - 1, 0) = sh.Cells(i, 1)
                    .List(ListBox10.ListCount - 1, 1) = sh.Cells(i, 2)
                    .List(ListBox10.ListCount - 1, 2) = sh.Cells(i, 3)
                    .List(ListBox10.ListCount - 1, 3) = sh.Cells(i, 4)
                    .List(ListBox10.ListCount - 1, 4) = sh.Cells(i, 6)
                    .List(ListBox10.ListCount - 1, 5) = sh.Cells(i, 5)
                    .List(ListBox10.ListCount - 1, 6) = sh.Cells(i, 7)
                    .List(ListBox10.ListCount - 1, 7) = sh.Cells(i, 8)
                End With
            End If
        Next X
    Next i
    Me.TextBox10 = Format(StrConv(Me.TextBox10, vbUpperCase))
   
End Sub

 

[jayjay2022]

Hello Jayjay2022, welcome.
It's hard to explain why this happens.
I can only say textbox "Change" event is little bit tricky.
Try in this way and I hope you will see the difference.

Private Sub textBox10_Change()

    Dim sh As Worksheet
    Set sh = Sheets("Daily Jobs")
    Dim i As Long
    Dim X As Long
    Dim p As Long
    Me.ListBox10.Clear

    a = sh.Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To a
        For X = 1 To Len(sh.Cells(i, 2))
            p = Me.TextBox10.TextLength
            If UCase(Mid(sh.Cells(i, 2), X, p)) = Me.TextBox10 And Me.TextBox10 <> "" Then
                With Me.ListBox10
                    .AddItem sh.Cells(i, 2)
                    .List(ListBox10.ListCount - 1, 0) = sh.Cells(i, 1)
                    .List(ListBox10.ListCount - 1, 1) = sh.Cells(i, 2)
                    .List(ListBox10.ListCount - 1, 2) = sh.Cells(i, 3)
                    .List(ListBox10.ListCount - 1, 3) = sh.Cells(i, 4)
                    .List(ListBox10.ListCount - 1, 4) = sh.Cells(i, 6)
                    .List(ListBox10.ListCount - 1, 5) = sh.Cells(i, 5)
                    .List(ListBox10.ListCount - 1, 6) = sh.Cells(i, 7)
                    .List(ListBox10.ListCount - 1, 7) = sh.Cells(i, 8)
                End With
            End If
        Next X
    Next i
    Me.TextBox10 = Format(StrConv(Me.TextBox10, vbUpperCase))
  
End Sub

Hi EXCEL MAX.

I used the code you provided and it works flawlessly.

I have been using VBA over a month now and the most frustration issue I have are dates and formatting dates.

I truly appricate your help.

 

[EXCEL MAX]

I'm glad if this was helpful.