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:
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
- 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).
Code:
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
Code:
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
