PDA

View Full Version : Search as You Type ~~ Narrow down List



Roam2k2
08-04-2008, 03:48 PM
Alright, so what i'm trying to do is have a box ( be it a combo box or list box ) and as you start typing, options will appear that correspond to what you type. For instance, if the list box has item's 'Apple', 'Banana', 'Cake', 'Anger'......As you type, 'A'...Apple and Anger should appear and when u type further it simply narrows down the list. It's like in facebook, when ur searching for one ur buddies...how it narrows down the list...hope that's clear enough... Any ideas how i'd go about this ? I've searched numerous sites and couldn't piece together a solution (most of the solutions were for vb6)...Thanks for all your help. .

- Roam.

malik641
08-04-2008, 05:01 PM
Is this "box" on a userform? If so, a combo box has that behaviour by default. The property is called MatchEntry and it is defaulted to fmMatchEntryComplete.

The only thing is the list doesn't "open" down for you to see your options.

Is that what you are looking for?

EDIT: I think I see what you mean. You're basically looking for a dynamic combo box. I haven't done anything like that before....but it's worth a try!

malik641
08-04-2008, 07:19 PM
Wow. This was much easier than I thought it would be.

I made a class so the code is cleaner. It's really easy to use. You need your dictionary of words on a worksheet in column A without a header. Then in VBA you need to set the worksheet to your the one with your dictionary in it. I used a sort method in my class because I was going to use it for something else...but it doesn't really hurt too much to have it where I do (in the Userform1_Initialize event).

Here's the code for the userform (with a combo box in it):

Option Explicit

Dim words As WordFinder

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If LenB(Me.ComboBox1.Text) > 0 Then
Me.ComboBox1.List = words.getWordsStartingWith(Me.ComboBox1.Text)
Me.ComboBox1.DropDown
Else
Me.ComboBox1.Clear
End If
End Sub

Private Sub UserForm_Initialize()
Set words = New WordFinder
words.DataWorksheet = ThisWorkbook.Worksheets("words")
words.SortData

ComboBox1.Clear
End Sub
And that's all there is to it!

Here's the class:

Option Explicit

Private wsData As Excel.Worksheet

Public Property Let DataWorksheet(ByRef ws As Excel.Worksheet)
Set wsData = ws
End Property
Public Property Get DataWorksheet() As Excel.Worksheet
Set DataWorksheet = wsData
End Property

Public Sub SortData()
If wsData Is Nothing Then Exit Sub

Application.ScreenUpdating = False

' Data should be 1 column of words to choose from
wsData.Columns(1).Sort Key1:=wsData.Columns(1), Order1:=xlAscending

Application.ScreenUpdating = True
End Sub

Public Function getWordsStartingWith(ByRef start As String) As String()
If wsData Is Nothing Then Exit Function

Dim rng As Excel.Range
Dim firstAddress As String
Dim wordsArray() As String
Dim i As Long

ReDim wordsArray(0)

With wsData.Range(wsData.Cells(1, 1), wsData.Cells(wsData.Rows.Count, 1).End(xlUp))
' IMPORTANT: Use LookAt:=xlWhole to let the wildcard work correctly
Set rng = .Find(start & "*", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
firstAddress = rng.Address
Do
ReDim Preserve wordsArray(i)
wordsArray(i) = rng.Value
Set rng = .FindNext(rng)
i = i + 1
Loop While Not rng Is Nothing And rng.Address <> firstAddress
End If
End With

getWordsStartingWith = wordsArray
End Function

It's not perfect. But it's a good start at what you're looking for. :)

Attached is the file. I had to compress it because I tried the code with 25000+ rows and it's still pretty fast.

Adonaioc
08-05-2008, 05:29 AM
Im like where this is going and i downloaded your file but i dont understand how it works, where is the combo box?

malik641
08-05-2008, 06:03 AM
It's on the userform. When in excel go to the VBE (Alt+F11) then click view->Project Explorer and double-click the folder "Forms" and double-click the form then press F5

Kenneth Hobs
08-05-2008, 06:19 AM
I did not write a neat class as malik did but I did something a bit similar with a listbox on a userform. The listbox was filled with a named range. My code dropped all entries that did not contain all letters typed in sequence. e.g. Typing "mm" showed the entry Summer only since no other words had two m's in sequence.

Since this site says that I have to post 5 or more, I can't post my link. It is at ozrid as post 65707 if it interests you.

Hoopsah
08-05-2008, 06:19 AM
WOW!

I like this.

What you need now is to have a button on the userform "OK" that when clicked selects the text and enters it into a particular cell.

It is really good though.

RonMcK
08-05-2008, 06:43 AM
... my link. It is at ozgrid as post 65707 (http://www.ozgrid.com/forum/showthread.php?t=65707) if it interests you.
No need to attach the file. Click the link, above.

Cheers!

RonMcK
08-05-2008, 06:52 AM
Joseph,

I'm gettting a syntax error on compile when I attempt to run the code (on Mac Excel 2004); the line in question is:

Public Function getWordsStartingWith(ByRef start As String) As String()


Puzzled,

malik641
08-05-2008, 07:17 AM
That is strange. I wonder if Mac's don't want to handle functions that return arrays....??? I'm trying to find some info now...

malik641
08-05-2008, 07:26 AM
Hey Ron,

Just a thought...try changing the String() to Variant().

RonMcK
08-05-2008, 07:27 AM
Sometimes, like in 'A 'floating' rounded rectangle' I run into things like 'lib user32' which is a Windows-only resource, when I'm testing a downloaded solution. :banghead:

Okay! I'll try changing String() to Variant().

Cheers!

RonMcK
08-05-2008, 07:38 AM
Joseph,

It choked on Variant() but likes Variant.
When I run it, the code pops up the UserForm however the DropBox has nothing in it. After I enter "A" and click the down array the DropBox is still emply.

I'm going to try this, tonight, on my PC at home for comparison.

Thanks,

malik641
08-05-2008, 07:46 AM
Let me know if you find a workaround. I hate using Variant, but if you cannot avoid it then we have to live with it.

I don't own a Mac and I don't have some kind of VM that has Mac on it so I can't test it unfortunately.

Yeah it's tough to get around MS libraries like MS Scripting Runtime (and the one you mentioned) for Macs.

Well, good luck Ron!

e4asanka
06-03-2016, 07:11 AM
I cannot find the attachment.

mikerickson
06-03-2016, 07:45 AM
You might like this
http://www.ozgrid.com/forum/showthread.php?t=198770

SamT
06-03-2016, 11:11 AM
I cannot find the attachment.
The thread is too old.

e4asanka
06-14-2016, 04:11 AM
Thank you so much for taking the bother to help me. with your support I could complete my work. Thank you so much.

e4asanka
06-14-2016, 04:12 AM
Thank you so much for taking the bother to help me. with your support I could complete my work. Thank you so much.