Keyword Search returns neighbor cell value

[Gary1]

Hello,

I need help with a new portion of a UserForm.

Have a multipage on a userform. Working pages pull data from an access database and populate excel 2007 sheets. I use comboboxes to search for a single value and then in textboxs, display related value. (ie: combobox has zipcodes, and textboxs populate city/state/sales rep name and details. etc)

New page in the multipage works differently, and has me confused.
On Sheet3(Sheet3), i have 3 columns. First colum is product code, 2nd column is product description, 3rd column is product type (C = clothing, B=Book, etc).

Sample data on Sheet3:

ProductCode ProductDescription ProductType
1122 Blue kids running shoes C
1122A Red designer scarf C
1122B Book - Big blue whale B
1234B Book - Those Shoes B
1345A Red Prada purse C

Now, instead of populating a combobox with a primary key value, I want to be able to search (in a textbox, combobox, whatever) for a keyword under ProductDescription, and have my choices decreased as more letters are typed.

In other words, if using a textbox, if I typed "blue", I would see "Blue kids running shoes"; "Book - Big Blue whale".

Typing "red" would return "red designer scarf" and "red prada purse".

Typing "shoes" woudl return "blue kids running shoes" and "Book - Those shoes".

I would then like to select the correct one, and then it populate a textbox with the Product code. For example, if i searched for "blue", and saw "Book - Big blue whale", I could highlight it, as in a combobox dropdown or anything similiar, and a nearby textbox would show that product's ProductCode. In this case, "1122B".

I'm using excel 2007. I've searched this site and google with no luck. Any suggestions or examples would be appreciated.

I think its possible to set up a SQL query to to do this, but since i've already pulled the data into excel, am looking for a way to just query the data on sheet3 as shown above. Thank you!

 

[Gary1]

Search works, need to limit results by Product Type...

Ok, so I got a good start, but need some advice.

I went with a textbox for user search input. Below the textbox is a listbox (my first time using a listbox) to display the results.

When the form first loads, it pulls the data from access to the excel spreadsheet. I had to change the SQL string so the data on sheet3 now looks like this:

ProductCode ProductDescription ProductType
1122 - Blue kids running shoes Blue kids running shoes C
1122A - Red designer scarf Red designer scarf C
1122B - Book - Big blue whale Book - Big blue whale B
1234B - Book - Those Shoes Book - Those Shoes B
1345A - Red Prada purse Red Prada purse C

Basically Column A now displays the Product code, a hyphen, and the Product Description. The textbox will search Column B, and return any/all results from Column A.

To get the search to work, I created a sheet in excel called "Search". This sheet gets updated with matching records and then all records on the Search tab are displayed in the listbox. I'm sure there is a better/efficient way to do this. But for now it works like this:

On the userform, I added the following sub:

Private Sub Search2()
Dim FoundCount As Double
Dim rFoundB As Range
Dim MyTextB As String
Dim B As Range
Dim rB As Range

MyTextB = Me.txtFindB.Value

Sheets("Search").Range("A2:f65536").ClearContents

' Column B searched
If Trim(MyTextB) <> "*" And Len(Trim(MyTextB)) > 0 Then
With Worksheets("Sheet1")
 Set rB = .Range("b2:b65536")
End With
Set rFoundB = rB.Resize(1, 1)
Set B = rB.Find(MyTextB, After:=rFoundB, _
  LookIn:=xlValues, _
  Lookat:=xlPart)
If Not B Is Nothing Then
 firstAddress = B.Address
Do
bcopy = False
   ' copy search/match items to tab "Search"
   lr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1
    B.EntireRow.Copy Sheets("Search").Rows(lr)
    FoundCount = FoundCount + 1
Set B = rB.FindNext(B)
Loop While Not B Is Nothing And B.Address <> firstAddress
End If

Sheets("Search").Select

' need to find the last filled in cell in Column A
    Dim wsSheet As Worksheet
    Dim rngNext As Range
    Dim myRange As Range
    Set wsSheet = ActiveSheet
        With wsSheet
            Set rngNext = .Range("A65536").End(xlUp).Offset(1, 0)
        End With
rngNext.Select
    Set myRange = Range("a2", rngNext)


' Used a listbox called lbosearch to display search terms
With lbosearch
    For Each rngNext In myRange
        If rngNext <> "" Then .AddItem rngNext
    Next rngNext
End With
End If

End Sub

And then on the change event for the textbox, i added this code:

    lbosearch.Clear
    Call Search2

This works great and all, but now I need to implement the final step. In the last column (column C), is product type. I want to add a combobox where one can choose which type of product to search for. (ie: Clothing only, Books only, Everything). Default search would be Everything.

This will help limit the returned search list in the listbox. Also, it just makes sense. If you want to search for only the books, then only books should show up in the search list.

Any idea how to incorporate this into the above code?

Thank you!

 

[Gary1]

How to add a combobox to filter the search?

For those using Option Explicit, you will need to use this code. (It defines all the variables).

I still am trying to find a way to add a combobox to filter the limit/restrict the type of results displayed in the listbox.

Anyone figure it out yet?

Private Sub Foo2()
Dim FoundCount As Double
Dim rFoundB As Range
Dim MyTextB As String
Dim B As Range
Dim rB As Range
Dim firstAddress As String
Dim bCopy As Boolean
Dim lr As Long

MyTextB = Me.txtFindB.Value

Sheets("Search").Range("A2:f65536").ClearContents

' Column B searched
If Trim(MyTextB) <> "*" And Len(Trim(MyTextB)) > 0 Then
With Worksheets("Sheet1")
 Set rB = .Range("b2:b65536")
End With
Set rFoundB = rB.Resize(1, 1)
Set B = rB.Find(MyTextB, After:=rFoundB, _
  LookIn:=xlValues, _
  Lookat:=xlPart)
If Not B Is Nothing Then
 firstAddress = B.Address
Do
bCopy = False
   ' copy search/match items to tab "Search"
   lr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1
    B.EntireRow.Copy Sheets("Search").Rows(lr)
    FoundCount = FoundCount + 1
Set B = rB.FindNext(B)
Loop While Not B Is Nothing And B.Address <> firstAddress
End If

Sheets("Search").Select

' need to find the last filled in cell in Column A
    Dim wsSheet As Worksheet
    Dim rngNext As Range
    Dim myRange As Range
    Set wsSheet = ActiveSheet
        With wsSheet
            Set rngNext = .Range("A65536").End(xlUp).Offset(1, 0)
        End With
rngNext.Select
    Set myRange = Range("a2", rngNext)


' Used a listbox called lbosearch to display search terms
With lbosearch
    For Each rngNext In myRange
        If rngNext <> "" Then .AddItem rngNext
    Next rngNext
End With
End If
End Sub