Code to populate combo box with unique entries from column (in alphabetical order)

Im looking for some code to populate the list in ComboBox1 with all the unique entries (removing duplicates) in the Sheet EquipmentData in column C (from row 3 on), and list them in alphabetical order.
Thank you for you help.

Please Login or Register  to view this content.

Thank you for your responses. Tinbendr I'm not familiar with how to make record a column sort. Is this done in a Module?
Kenneth I tried pasting that code into the userform but it didn't seem to do anything. Does it require further action on my part to set it up? It didn't generate any errors. It put lines after the End Sub and between the two functions.

What is Excel 2011? There is a 2010 and a 2013 version. Did I miss something?

When you code in the Visual Basic Editor (VBE), always Compile your code before you run it. This will capture some issues. I use it so often I added it to the VBE toolbar. I use Option Explicit as the first line of code. This also can help you while you code if you have the option set to Require Variable Declaration.

As commented in my code, the dictionary object method that I used referenced the Microsoft Scripting Object. This makes intellisense work. You can choose to set the reference by the menu Tools > References as commented in my code or modify the macro to use late binding which does not require setting the object. That is easily done by commenting out two lines of code and uncommenting the other two. I explained this in my comments in the code.

I did not know which of the 3 combobox control methods you were using. I used an activex control on a sheet. For a userform combobox control, I would fill the combobox in the event that Tinbendr used as shown below. Typically, one would put the two functions into a Module for public use.

You put code after End Sub? Without seeing your code, I don't know what you mean. While I could post an example workbook, it is best for you to post an example workbook so that we can best help you.

Obviously, you need to change the Set r line of code to suit your sheet and data range. I used the activesheet's range of C3 to the last value in column C.

Please Login or Register  to view this content.

Thank you for all your responses.
John55, your code gave me Run-time error '1004', The sort reference is not valid.
Patel45, your code gave me Run-time error '381', Could not set the List property. Invalid property array index.
Kenneth I'm not sure I quite follow you, but your code gave me Compile error: sub or Function not defined.
If Tinbendr's code is the best way to go since I'm using Excel 2011 for mac, how do I '…record a column sorting…' to work with that code?

Some of the cells in the range of data contain blanks, isn't that an issue for pivot tables?

Hi All,

Happy New year. I had some brilliant assistance from this forum with a problem i had mid last year around VBA coding and a database that i have developed using Excel.

The coding seems to have changed an no longer works. Is any one able to help me please? I am still a novice when it comes to VBA coding. I can attach a sample of the database here for everyone to see if that would help.

Simply i would like the coding to allow me to move a row from one sheet to another based on a value in a column (in this case based on their qualification "coach", "referee", "Presenter" )

In addition to this i have a code that allows me to click a button and will then save the sheet as a separate workbook which allows me to send it with out sending the entire document. However i would like this button to lock the sheet at the same time when it saves. Is this possible?

Any help would be greatly appreciated.

Kind regards

JM88

Here is a collection and bubblesort method. The last function is what I use for a scratch worksheet sort method. I normally put public functions and subs into a Module though you can put them in the Userform object.

Please Login or Register  to view this content.

My Apologies, i will re post it in a new thread.

Tinbender that code didn't seem to do anything to my ComboBox, its just copied some random cells/rows into random places on the worksheet.
Kenneth, your code had some promising results, but it needs some tweaking. It correctly lists all the unique items in alphabetical order, however as soon as I type a single character it automatically enters the first thing it matches to that character into the combo box. What I would like it to do is filter the results in the drop down list so only values in the list containing that character remain. Then when a second character is entered it further filters more so only values where those two character appear next to each other (in the same order) remain etc etc. And the drop down box should remain visible as the user types, basically working as a auto recommend/filter for the user, so when the entry they are trying to type appears they just have to click on it.
Also it appears to reference the worksheet thats currently being viewed. I would like it to only reference the sheet EquipmentData please.
Thanks,
James

I specified the sheet in my first post, but I know that was quite a few posts back now.
Thank you for spending the time on this for me, I much appreciate it.

A Combobox control is very similar to a Listbox control. Here is where I explained how to do the wildcard filling method for a listbox control. http://www.ozgrid.com/forum/showthread.php?t=65805 and http://www.ozgrid.com/forum/showthread.php?t=65707

I attached a file showing the listbox method. Being as you have the mac version, I am not sure how much it would help. The unique and sorting methods that I explained can be added to this method to make it even easier.

It might be easier to help you if you attached an example. Then again, I don't know how well that would work for my Microsoft 2010 version.

I have attached my workbook. The userform I'm working on is called AutoFillText.
What I'm aiming for is for the user to type into that combo box, with then filters at unique alphabetically listed entries, which are always dropped down and showing to the user so they can select the one the want when they see it. The list of the raw data its should be referencing is on EquipmentData sheet in C column (from 3 on).
If its easier i suppose it could be done by have the user just type into a text box, while the results are filtered and displayed in a List box, so when the user makes a selection in the filtered list box it then copies it into the text box.

Had you implemented post #23 code, it would have listed the correct range.

It is not recommended that you post a whole workbook when asking for help. Just delete everything that does not pertain to your problem. Isolating problems lets you solve problems more readily.

As for filtering, the option was set for the dropdown list to be matched as they type. If they want to see the list, the dropdown arrow does that for them. I guess if you want to drop it for them, there are two methods to do that. The method combobox1.dropdown should do it and does but it tends to fly up to the top left of the application. Sendkeys is a poor method and since you use a mac, it would not be viable most likely.

Please Login or Register  to view this content.

I implemented the code on #23, which directed the ComboBox to the correct data, but it still has the same issues I listed in #22 (its not filtering the results as the user types). I tried the workbook UniqueSortComboBox, but I get run time error '5': Invalid procedure call or argument. With the line in bold:

Please Login or Register  to view this content.

I tried just removing the Private Sub ComboBox1_Enter() section of code, but it has the same issue as with post #22 (not filtering the results as the user types).

I don't see anything in bold. I am sure that it was probably the SendKeys issue as I explained. Comment that line out and then uncomment the combobox1.dropdown and see if that works as I explained.

Do you really need filtering? The way you have it set up is the default to match on entry. It matches as you type. If you want to see the data then click the dropdown button and then type. It jumps right to the first match. Do you see what I mean?

For true filtering as I demonstrated in the attachment #25, more involved code is needed. I can do that but I fear you will still have problems if code or the user does not activate the dropdown. Maybe a listbox would be better for you if you are forcing the user to pick an entry.

Sorry, it does need to be filtered. I would be fine with using a list box say below a text box. Say the user types into the text box and the list box below filters as the user types. Then the user can select an entry from the list box when they see the item they want which then gets copied into the text box.

When I get time, I will do the Filter for you.

Most people find the autocomplete sufficient. To illustrate that with a small tweak, I did this 2nd example using a listbox and some labels to show the user what they typed. A backspace key as noted in a label shows how to reset the autocomplete text and resets the type ahead buffer to try again.

Please Login or Register  to view this content.

Here is the Filter method. One good or bad thing about this method is that if "any" substring exists in the array, it is filtered to be listed. It needs a tweak or two to handle cases of empty filtered array and such.

Please Login or Register  to view this content.

Userform 2 matches an entry from the first letter typed, but no longer filters the list down as I continue to type more letters. It also doesn't seem to be removing the entries that dont match the letter typed from the list.
On Userform 3 as soon as I typed the first letter it gave me "Run time error 380, Could not set the List Property. Invalid property value." on the following line:
ListBox1.List = Filter(a(), Label2.Caption, True, vbTextCompare)

Thank you for your help Kenneth, I guess it must be an issue with Excel 2011. I will look into other options.