How can I get my combobox list to fill with a range without using rowsource property?

Hello again,
This is question 2 of 3. The 1st was solved by bakerman2...a BIG thanks to him for his help.
In the attached I have a simple userform with 3 comboboxes. I am currently using the RowSource property for the comboboxes to fill them with the proper list. What I would like to do is go through Column A in the Lists sheet and fill in combobox1 with the cells that are not blank starting in row 2. Also, if the user types something that is not in the list, add it to the column.


BTV Excel Forum Sample.xlsm

Please Login or Register  to view this content.

If you insert a blank column between A & B and B & C then you could use this.

Please Login or Register  to view this content.

PS Don't forget to remove RowSource from Combo's before running the code.

Thanks to both of you for your code!. Each of the codes you guys provided did populate the lists like I wanted, but, neither one of them will add to the list in the sheet lists if a user hand keys into the combobox and it doesn't exist on the list already.

@bakerman2 - What is the purpose of inserting the blank columns? I tried it without and with and the only difference I noticed is that it added a lot of empty space at the end of the list of the comboboxes when I didn't insert a blank column as opposed to only having 1 empty space with the blank columns inserted. I'm guessing this is the reason for the inserted blank columns, to eliminate most of the empty space.
With AlphaFrog's code, there was no empty space at the end of the lists.

Edit* I forgot to ask this in my original post. Since I'm allowing user input to be added to the list(s), is there a way to auto sort those lists from A to Z when a new item is added?

Originally Posted by gmr4evr1

Thanks to both of you for your code!. Each of the codes you guys provided did populate the lists like I wanted, but, neither one of them will add to the list in the sheet lists if a user hand keys into the combobox and it doesn't exist on the list already.

@bakerman2 - What is the purpose of inserting the blank columns? I tried it without and with and the only difference I noticed is that it added a lot of empty space at the end of the list of the comboboxes when I didn't insert a blank column as opposed to only having 1 empty space with the blank columns inserted. I'm guessing this is the reason for the inserted blank columns, to eliminate most of the empty space.
With AlphaFrog's code, there was no empty space at the end of the lists.

Edit* I forgot to ask this in my original post. Since I'm allowing user input to be added to the list(s), is there a way to auto sort those lists from A to Z when a new item is added?

If the user adds to the lists before they load the userform, the comboboxes will include the new items when the userform loads.

If you want them to be able to add to the lists while the userform is already loaded, then make a command button to update the lists and use the same code I gave before for the command button.

You can record a macro where you sort the lists and then copy that code to your existing code.

This is the corrected code. My apopolgies for posting wrong code.

The reason you insert a column between is that CurrentRegion takes all adjacent columns and rows so defining the range for each combobox would take more code than now.

Please Login or Register  to view this content.

Here's code to ask the user if they want to add a new item if they enter an item not on the list.

It's for ComboBox1 but the same idea could be applied to the others.

Please Login or Register  to view this content.

@ AlphaFrog - The userform will be initialized when the workbook opens. I had code once before that would allow a user to type into the combobox and if what they typed wasn't already on the list, it would add it to the list from the combobox, the problem is, I cannot find where I saved the code to do this.

@bakerman2 - The code you provided in post # 6 is the same as the code in post #3...isn't it? Or am I missing something.

@Norie - If I can't find the code I had before to add to the list, or if what I'm asking isn't possible, I will use the code you provided.

It's indeed the same as in post#3 but I corrected that one too just for clarity (Previous code all lines said Combobox1)
This code checks for Combobox1 but can be adapted for the other 2.

Please Login or Register  to view this content.

Edit: Have added the code for all 3 CB's. Just fill with 3 non-existing values and click Submit-button.

Sorry Guys

I do not see how the above solutions get rid of the blank entries in the Comboboxes.

This solution loads comboboxes 1 to 3 with the data in columns A to C without any blanks.

Please Login or Register  to view this content.

Sorry everyone, it took me a while to be able to get bake to this and after some testing I've found a few issues.

Bakerman2 - After some discussion with the end users we determined that the only column we needed the list to update for was C on the Lists sheet. I've modified your code to reflect this but I have run into a problem. It seems to add items to the list that DO exist on the list already, as well as items that don't exist. On Sheet1, row 16 after I typed New into combobox3 of the userform and clicked submit, it added New to the list in column C of the List sheet as it should have (see C12 on the Lists sheet). I then ran the workbook open code again and selected New from combobox3 then clicked submit on the userform and New was added to column C of the List sheet a 2nd time (see C13 on the Lists sheet) I'm not sure why it is adding already existing items to the list.
Bakerman2 BTV Excel Forum Sample.xlsm

Norie - The code you provided worked great and I am considering using it for this application. Even if it's not used for this one, it will be used in future ones. Thank you very much.

Mehmetcik - I tried the code you provided and was not able to get it to work. With Option Explicit being used, it would error at the For Count line (with Count highlighted) with a Compile error: Variable not defined. I removed Option Explicit and would get this error - Compile error: Method or data member not found on the

Please Login or Register  to view this content.

with .Controls highlighted.
mehmetcik BTV Excel Forum Sample.xlsm

Please Login or Register  to view this content.

bakerman2,
That did it!! Thank you [all] so much for your help!!

Glad to help and up to question 3 now ?

And I'm glad you offered to help.
I will be posting that thread (3) later today or tomorrow.

While searching for the code for question 3 posted here
https://www.excelforum.com/excel-pro...ml#post4780713
I found the code that I was originally looking for when I asked the question in this post "How can I get my combobox list to fill with a range without using rowsource property?", which is question 2 of 3.
Here is the code, modified to suit my needs...

Please Login or Register  to view this content.

Here's the kicker....I'm the one that provided the above code for another member in this post..it's post #2
https://www.excelforum.com/excel-pro...-userform.html