Userform Listbox Not Displaying Correctly

[zamber8]

I have a Userform with 3 textboxes ("Textbox1-3") that search all active cells on sheet ("Locations") and list the found rows in the listbox ("Listbox1"). The Listbox should display 13 columns and is only used for viewing. I'm having trouble with 3 things:

• When the results are displayed it is displaying column A from the sheet in column B on the listbox and so on leaving the first column in the listbox blank. I want the results to transfer over on the listbox the same way as the sheet displays.

• In addition, it is searching/displaying the first row (which are the titles so shouldn't be searched)

• Also, when I put in the count on the code below as anything over 9 I get a "Run-time error '380': Could not set the list property . Invalid property value." therefore only displaying 10 columns (first one blank and other 9 with data).

For Count = 1 To 9
ListBox1.List(ListBox1.ListCount - 1, Count) = Cells(UniqueItem(n), Count)

I've spent days looking at the code and searching the internet with no luck. I'm sure it is something very simple however I'm new at this and unable to figure it out! Thanks so much
-Amber

using Excel 2013

Public records As Variant
Option Base 1

Private Sub TextBox1_Change()
SearchText
End Sub
Private Sub TextBox2_Change()
SearchText
End Sub
Private Sub TextBox3_Change()
SearchText
End Sub


Private Sub SearchText()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("locations")
ws.Activate
Dim temp As Variant
Dim UniqueItem As Collection

Sheets(Label14.Caption).Select

temp = ThisWorkbook.ActiveSheet.UsedRange.Address
TextLen = 0
Searchbox = 1

For Count = 1 To 3
If Len(Me.Controls("Textbox" & Count).Value) > TextLen Then
TextLen = Len(Me.Controls("Textbox" & Count).Value)
strValueToPick = Me.Controls("Textbox" & Count).Value
End If
Next

If TextLen < 1 Then Exit Sub

On Error Resume Next

    With Range(ThisWorkbook.ActiveSheet.UsedRange.Address)
        Set rngfind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart)
        If Not rngfind Is Nothing Then
            strFirstAddress = rngfind.Address
            Set rngPicked = rngfind
            Do
                Set rngPicked = Union(rngPicked, rngfind)
                Set rngfind = .FindNext(rngfind)
            Loop While Not rngfind Is Nothing And rngfind.Address <> strFirstAddress
        End If
    End With
    
    If strFirstAddress = "" Then Exit Sub
    If Not rngPicked Is Nothing Then
        rngPicked.Select
    End If

ListBox1.Clear

Set UniqueItem = New Collection
    
For Each c In Selection

RowText = Join(Application.Transpose(Application.Transpose(Range(Cells(c.Row, 1), Cells(c.Row, 13)).Value)), " ")
If Len(TextBox1.Text) > 0 And InStr(LCase(RowText), Trim(LCase(TextBox1.Text))) = 0 Then GoTo 20
If Len(TextBox2.Text) > 0 And InStr(LCase(RowText), Trim(LCase(TextBox2.Text))) = 0 Then GoTo 20
If Len(TextBox3.Text) > 0 And InStr(LCase(RowText), Trim(LCase(TextBox3.Text))) = 0 Then GoTo 20

On Error Resume Next

UniqueItem.Add CStr(c.Row), CStr(c.Row)

On Error GoTo 0

20 Next c

For n = 1 To UniqueItem.Count
    ListBox1.AddItem
UniqueItem (n)


For Count = 1 To 9
   ListBox1.List(ListBox1.ListCount - 1, Count) = Cells(UniqueItem(n), Count)
Next
Next

End Sub

 

[Leith Ross]

Hello Amber,

It should be checking column 1. I will take a look at the code again.

 

[Leith Ross]

Hello Amber,

I just ran the last updated version of the code. Column 1 comes up for me when I type in Text Boxes 1 - 3.

Have you tried saving the workbook, closing Excel, and then reopening the workbook?

 

[zamber8]

Yes I've closed it and re-opened. Also, moved it to another workbook.

The same code you created for me is used in two other userforms that search for data on other worksheets and they both are searching the first column as they should it is just this one that isn't.

I took the code on the ones that are working and compared that with this one and the code all looks identical so I'm not sure why it isn't working. Apparently somewhere within I have clicked/changed something to make it not work but I'm not able to locate where!

https://www.mediafire.com/?2o3b23hvpah3com

 

[Leith Ross]

Hello Amber,

The problem appears to be with matching dates. VBA stores the dates as numbers and formats them as text. This causes the date not to be found by the Find method. I am working on an alternate search method.

 

[zamber8]

Thanks so much Leith! You have saved me I know what probably would have took weeks to figure this out. My project is almost complete! I thought I was going to have to scrap the whole thing after doing research and learning that shared workbooks aren't a good idea since we really need multiuser (12) however transferring/starting over in Access I think would be a large job so we will just have to settle with one user able to use the file for editing at a time since I'm nervous to turn on workbook sharing.

When text is searched in textbox1 it is showing the results the way it should. However when search criteria is entered into textbox2 instead of further narrowing down the current results found from textbox1 it is starting the search over and disregarding textbox1's search results. So in the workbook that was sent over if "Indian" was typed in text box1 it shows all results with "Indian" (for "Bob Williams" and "Ricky Jones") then when "Bob" is added to textbox2 instead of now eliminating the ones for Ricky Jones it shows all of "Bob William"s records regardless if "Indian" is in the row or not.

Thanks again for all of your time on this.

Amber

 

[zamber8]

Would it help if a Datepicker was added to search the dates and then the remaining 3 textboxes would just search the text?