Excel 2007 : How to add items to a dropdown list dynamically by typing in the dropdown list cell?

Your best bet is to make sure the drop down items are in a range of cells somewhere. Let's list them in column AA on the current sheet.

1) Put in some items in column AA

2) Press CTRL-F3 to open the Name box

3) Create a name range called OPTIONS that refers to:
=OFFSET($AA$1,0,0,COUNTA($AA:$AA),1)

4) Put a data validation formula in A1 for Allow: List > Source: =OPTIONS
...also click on the Error Alert tab and uncheck the "[ ] show alert"

5) Right-click on the sheet tab and select VIEW CODE

6) Paste in this code:

Please Login or Register  to view this content.

6) Close the editor and save your sheet as a macro-enabled sheet.

==========
Now, the drop down in A1 will list all the items in column AA. If you manually enter a new value, the macro will add the new item to the column AA and sort the list...updating the drop down for the next time it is used.

Thank you very much! This is exactly what I wanted. I have a few questions though, as I have to use the list in multiple tabs and many times.

How can I create the list in another worksheet and call it from there, instead of creating the list on the same sheet where my validation list is?

I am going to use three workbooks with 40 tabs each, so how do I implement this code so that I don't have to paste this code for each of the 40 tabs in the three workbooks? Can I do something to have the code functionality available for the whole workbook, so that even if I create a new tab/worksheet and paste the content of one of the 40 tabs along with the dropdown lists, the code will function in the new worksheet without any additional programming?

I modified your code in the following way to make the code available for the whole workbook, but it is not working (I am not surprised as I don't have any VB coding knowledge):

Please Login or Register  to view this content.

I have the above code in Sheet1, and not in WorkBookObject as I don't know how to put the code in WorkBookObject to apply it to the whole workbook.


I have another code that will be running which enables multiple selection of items from the validation list. The following is the code:

Please Login or Register  to view this content.

Can I also do something that I don't have to copy the above code in each tab of the 40 tabs on the three workbooks?

Edit: I just found out that the above mentioned codes (your code and the one that use for multiple item selection) do not run on the same page in "View Code" of one sheet (code pasted as follows):

Please Login or Register  to view this content.

It gives me an error saying "Compile error: Ambiguous name detected: Worksheet_Change." Is it because both the codes have "Worksheet_Change" in them? I was just trying to see if these codes worked in harmony on one sheet. Of course, I am looking for an option that would enable me to run the codes on all worksheets by applying them to a workbook as opposed to just one worksheet.

I hope your eyes discover my post as I have to provide the codes tomorrow. Thank you very much for your help. I truly appreciate it.

Edit: I have made some changes in the workbook after some research and I am attaching my workbook with this post. The problem still remains. Now that I have merged both the codes, I am able to only make multiple selections, and not able to add items in the dropdown menu. My range is in sheet2, and the dropdown menu is in Sheet1 in cell A1. The code is in "Thisworkbook."

Keith.

You may find this website useful:
http://www.mvps.org/dmcritchie/excel...m#wb_shtChange

I believe the event you're looking for is:

Please Login or Register  to view this content.

OK, reordered like so:

Please Login or Register  to view this content.

Thank you very much again! It worts!! The only thing I am wondering about now is the following:


Now I want to edit the code so that I can make the application to all the sheets, instead of applying just to Sheet1 in the following line in the code:

I want to edit the code attached so that I can include more columns in addition to A1 columns of all sheets, not just Sheet1. My Excel template would have several named dynamic lists on one sheet (sheet2 in this example)and those lists would be pulled by many dropdown menus based on those lists in several cells in different worksheets. As an example, I have two lists in colums A and C in Sheet2. I have two dropdown menus in Sheet1 in cell A1 and E15. The code I have so far allows multiple selection only from the range in column A of sheet2 in cell A1 of sheet1; it doesn't allow those functionality for the list in column C of sheet2 in cell E15 of sheet1. Again, as I mentioned, I am struggling to have the multiple selection and add items to the dropdown menu functionality available in any sheet on any cell, not just sheet1. I hope I have defined the problem clearly. Thank you.

Like so. You can add more sections at the bottom as you develop more lists. I've highlighted the things I changed to make it function on all sheets:

Please Login or Register  to view this content.

I am using the following code:

Please Login or Register  to view this content.

It works, but I wanted to make sure it accomplished the same functionality your code does.

You code unnecessarily allows the macro to do calculations in the background anytime ANY cell in columns 1 and 5 are changed. Mine restricts activity to the specific cells so no background activity goes on unless it is appropriate to occur.

Like so:

Please Login or Register  to view this content.

You misused the Lists. property because that's an actual VBA function, that's why it didn't register as an error. You have to describe sheets in the standard format:

Please Login or Register  to view this content.

I still wouldn't use your Target.Column code line.

Also, you need to take care redesigning columns that were set to AUTOSORT. I had to change the sort column syntax to completely ignore the first two rows, something it wasn't doing originally.

Maybe this:

Please Login or Register  to view this content.

Thank you SO MUCH! It has been a HUGE help with all the insight and inputs you have provided and everything is working fine. I have one final thing to ask. I have addedd the codes for all the lists for one sheet in one workbook. I have 40 sheets each in 7 workbooks. I have created macros for adding data validation lists on one sheet. Is it possible I can replicate all the macros for all the sheets, instead of running macros on each sheet for each of the lists in each workbook? I attach my template I am working on. Thank you very much.

Edit: I tried grouping the tabs and trying to add macros to a range fo cells in one sheet assuming it woudl add to the same range of cells in other worksheets too, but it didn't work.

You've lost me completely.

Going backwards, the WorkBook_SheetChange event we created was working for me on two separate sheets, not just one. Any changes made in either of the sheets in a cell with Data Validation was causing the lists on the LISTS sheet to add values when needed.

There's no reason that it should stop working just because you went up to 40 sheets.

This code:

Please Login or Register  to view this content.

...In the THISWORKBOOK module means "do this on every sheet".

Put the data validation into all the sheets as needed. Put the most uptodate version of the code as I provided into the ThisWorkbook module.

Save that and upload it and I'll take a look. As shown, it's working for me.

Hello:

I am sorry I could not respond in the last few days as I have been traveling and using the template. I have come across a new situation now. In all the data validation dropdown menus, how do I include the functionality that when I type a part of the name of the item in the list, the item appears sorted in the list. For example, if the item in the dropdown list is "Item1," when I type "Ite," the dropdown list sorts itself to show all the matching items beginning with "Ite" - how do I achieve that? Thank you very much for your help.

Start a new thread and ask that question. It's a completely different topic and for more convoluted than you can imagine.

EDIT the original first post in this thread and add [SOLVED] to the beginning of the title (you might have to GO ADVANCED)...then start a new thread.