Printable View
Im trying to create a searchable combo box on a worksheet. There is a #Value error in cell F7
Does anyone see the error? The issue seems to be the wildcard "?*" Workbook is attached. Thanks.Code:=$D$2:INDEX($D$2:$D$12,COUNTIF($D$2:$D$12,"?*"))
If I can get this to work is there a way to do this in a userform?
What is the purpose? What result were you expecting?
Hi Ken. I got it sorted. Is there a way to use a combo bos like the one in the WB attached in a user form?
It is an ActiveX control so, yes, same method can be used.
so on the combo box in the userform would the VBA CLICK code be. Userform.1ComboBox1.Employees? Or do you have to use a module or reference the sheet?Quote:
Originally Posted by Kenneth Hobs
Either the Change event or Click event gets you close to what the other does. Of course rather than ListFillRange=Employees set the RowSource=Employees.
Code:Private Sub ComboBox1_Change()
ComboBox1.DropDown
End Sub
Private Sub ComboBox1_Click()
'ComboBox1.DropDown
End Sub
that got it to show the list but instead of just showing the names say with "am" in it like the ones on the combo box one the sheet does the drop down shows all names at all times. Update WB with form attached.
I have about 3 ways to do it I think. I will try a few 8 hours or so from now and show you.
thanks kenQuote:
Originally Posted by Kenneth Hobs
Here is the Filter() method.
Code:Private Sub UserForm_Initialize()
Dim a
a = Application.Index(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value, 0, 0)
ComboBox1.List = a
End Sub
Private Sub ComboBox1_Change()
Dim a, s() As String
On Error Resume Next
a = Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)))
With ComboBox1
If .Value = "" Then .List = a
s() = Filter(Application.Transpose(.List), .Text, True, vbTextCompare)
If UBound(s) <> -1 Then .List = Application.Transpose(s)
End With
End Sub
Private Sub btnExit_Click()
Unload Me
End Sub
Here is a 2nd method using your Sheet's ActiveX Combobox's ListFillRange method with your dynamic named range.
For a large list, the 1st method would probably be best. For short lists, this 2nd method is more simple providing your setup for dynamic range and formulas are already done.
Normally, I prefer Filter() since I like to remove duplicates and sort.
Code:Private Sub UserForm_Initialize()
With Sheet1
[G1] = ""
ComboBox1.RowSource = .[Employees].Address(external:=True)
End With
End Sub
Private Sub ComboBox1_Change()
With Sheet1
[G1] = ComboBox1.Text
ComboBox1.RowSource = .[Employees].Address(external:=True)
End With
End Sub
Private Sub btnExit_Click()
Unload Me
End Sub
interesting....thanks for that but i do have a question. In the latest WB say you type in "e" you get all names in the drop down that contain the letter "E", "f" the same thing. But if yo start to type "G" there are at least to names wit the letter "g" in them but what it does is bring up the first name in the list.
What appears to be happening is that if a name does not start with a letter you are typing all entries with that letter show. If there is a name that starts with that letter only the first occurrence is presented and not all names with the letter in it.
Some combobox properties are critical to get the drilldown that you want.
This one has the 3 methods. It has a 4th, or 3rd userform but don't run it. It was an old method that I used for ListBox drilldown years ago. It is a less efficient way anyway.
I did a small tweak to the Filter method. Now, if one backspace deletes, it will go back to that wildard drilldown.
i like the dynamic combo box. i noticed it fills the other combo box with the potential matches (at least that is what it appears to do). Is there a way to just use the dynamic combo box and have the filtered entries show as you type? also what code goes in the module?
Yes, it uses the same setup as the sheet1.combobox1. So, your formulas and dynamic named range need to be there. Since it uses the same method by modifying G1, the ListFillRange in sheet1.combobox1 adjusts as well.
Code for it is in the userform ufFilterCombo2 which is also shown in post #11. I guess you could put it in a Module. If you had many userform's using that method, I guess one might pass MSForms.Combobox to such.
I prefer to let the user click the dropdown arrow but if you like, in ufFilterCombo2:
For many such drilldown lists, I would use the Filter() method. I don't want to worry about setting up all of those formulas and dynamic named ranges. But then I am lazy...Code:Private Sub ComboBox1_Enter()
ComboBox1.DropDown
End Sub
hi ken. im going to have around 70000 names to search using this. the list is also going to be updated once a month. do the entries have to be sorted in order for this to work? it appears that they dont because you are searching characters and not full names so the order doesnt matter, correct?
Right, it is not needed.
If I were the end user, I would want duplicates removed and sorted.
thanks for all the help. if i were to incorporate this (the filter method) into this WB/form, where would the code go and what code would be needed. Im not sure since we have been through a bunch of examples.
In the form that opens on the attached WB, I would like to replace the account name text box to a combo box to perform this search. Would you please tell me where to make the adjustments? Thanks.
You lost me. I don't know what the textbox control name is. I don't know what column of data you want to filter in it.
Post #13's 1st userform has the code similar to post #10.
Code:Private Sub UserForm_Initialize()
Dim a
a = Application.Index(Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value, 0, 0)
ComboBox1.List = a
End Sub
Private Sub ComboBox1_Change()
Dim a, b
On Error Resume Next
a = Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)))
With ComboBox1
If .Value = "" Then .List = a
'b = Filter(Application.Transpose(.List), .Text, True, vbTextCompare)
b = Filter(a, .Text, True, vbTextCompare)
If UBound(b) <> -1 Then .List = Application.Transpose(b)
End With
End Sub