# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  Code to populate combo box with unique entries from column (in alphabetical order)

## Nitefox

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.

----------


## Tinbendr

As far a sorting, you can record a column sort and put that code in a separate sub and call it just before the combobox fill routine starts.



```
Please Login or Register  to view this content.
```

----------


## Kenneth Hobson

```
Please Login or Register  to view this content.
```

----------


## Nitefox

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.

----------


## john55

or 



```
Please Login or Register  to view this content.
```

----------


## patel45

In order to semplify the code, with data in column C and Column Z available for service



```
Please Login or Register  to view this content.
```

----------


## Izandol

If you have Excel 2011 I think only Tinbendr code will work for you.

----------


## Kenneth Hobson

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.
```

----------


## Izandol

Excel 2011 is the most recent Mac version of Excel. It may not use Scripting library or .Net libraries.

----------


## Kenneth Hobson

Good to know.  I don't do mac so that would definitely be an issue for mac users.   I guess there should be an Excel forum for mac users.  But looking at the user's profile, I should have noticed the 2011 mac which is the problem.

Since that is the case, a collection method to remove duplicates and a worksheet or scratch worksheet sort would suffice.

----------


## Nitefox

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?

----------


## john55

oops, did not see you want to use 2011 Mac...

----------


## MarvinP

Hey Nitefox,

Why don't you do a single column Pivot Table using your data, which will alpha it and remove dups.  Then use that range (in the Pivot Table) for your combo box rows.

Easy Peasy...  Why do you want to write code to do stuff that doesn't need it?

----------


## Nitefox

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

----------


## MarvinP

Cells being blank is ok but a whole row or column can't be blank.

----------


## JM88

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

----------


## Izandol

I think this is a separate question.

----------


## Tinbendr

What I'm trying to get you to do is record a macro.  There are several good tutorials on the net.

I decided to just do it an incorporate it.

Make sure you change the range to include the whole sheet.

We're sorting the worksheet before we read the values.




```
Please Login or Register  to view this content.
```

----------


## 6StringJazzer

> Is any one able to help me please?



JM88, as a new user I recommend you please take the time to review our rules. There aren't many, and they are all important.

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Kenneth Hobson

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.
```

----------


## JM88

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

----------


## Nitefox

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

----------


## Kenneth Hobson

The issue of getting the correct range is minor.  I just used the activesheet since that is the default parent object of the Range object when not specified.  If you want some other range in another sheet that might not be the activesheet then prefix Range with Worksheets("WhateverSheetNameYouWant").  e.g.



```
Please Login or Register  to view this content.
```


As for the filtering of a data list that is unique and sorted, as you type characters, that is a more involved process.  I have done that sort of thing but adapting it for mac code might take a tweak or two.  I will look for that example later today and see if I can modify it for you.

----------


## Nitefox

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.

----------


## Kenneth Hobson

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.

----------


## Nitefox

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.

----------


## Kenneth Hobson

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.
```

----------


## Nitefox

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

----------


## Kenneth Hobson

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.

----------


## Nitefox

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.

----------


## Kenneth Hobson

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.
```

----------


## Kenneth Hobson

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.
```

----------


## Nitefox

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)

----------


## Kenneth Hobson

I can not duplicate your findings in the file posted to post #32.  

As I explained AutoFillText2 does not remove entries.  It does auto match as you type.  Try typing "test" less the quotes.  It should drill down to the entries starting with test. If you then type 2, it will skip the 2nd test entry and jump to test2. 

For AutoFillText3, I explained that it might error under some conditions.  I tried several keys but could not find one that would cause an error.  It may just be an issue where you run the mac version and I run Microsoft 2010.  IF you tell me the key(s) that cause an error, I can test that.  Of course On Error Resume Next before the Filter might help you.  For me, when no match was found, the list was cleared as one would expect.  Pressing backspace key refills the list and removes the keys with no match.  Those match keys are shown in label2.

----------


## Nitefox

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

----------

