+ Reply to Thread
Results 1 to 22 of 22

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

  1. #1
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

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

    Hello:

    This is my first post here, so please let me know if I am miss providing any information that would help solve my problem. I have the following need in Excel:

    I have a dropdown list of 3 items in cell A1. I want to be able to add items in the dropdown list by entering them into cell A1, not by creating a dynamic range elsewhere. I am required to captured new items in the list as and when they are provided during a data gathering session, so when I come across a new item which is not in the dropdown list, and when I type that item in cell A1, that item should be the entry for cell A1 and also be added to the dropdown list of cell A1. Could someone please provide some detailed method to accomplish this? I have a deadline to meet today.

    I am using Excel 2007. Thank you.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

  3. #3
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.
    Attached Files Attached Files
    Last edited by incisivekeith; 11-23-2009 at 12:02 AM.

  4. #4
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    Update:

    I have been reading and modifying the code and have finally reached this stage where I think I can make multiple selections and can also add items to the dropdown menu, but every time I add an item to the dropdown menu, the debugger gives an error saying Intersect failed. Could you please take a look at that and help me fix it?

    I have put the code for both the functionality (multiple selection and adding items to the dropdown menu) in Thisworkbook object - will it apply to the whole workbook? Please find the Excel file attached, where the dropdown menu is in cell A1 in sheet1 and the named range is on sheet2. Thank you.
    Attached Files Attached Files

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    OK, reordered like so:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.
    Attached Files Attached Files
    Last edited by incisivekeith; 11-23-2009 at 02:01 PM.

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

  9. #9
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.
    Last edited by incisivekeith; 11-23-2009 at 05:52 PM.

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

  11. #11
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    Thank you very much for leading me to this point. I thought I had it in now, but I came across another problem. In the part of the code where it adds to the dropdown menu in a particular cell, how would I edit it to have it so that I select a range of cells in a particular column (each cell in that range has a dropdown menu) instead of selecting only one cell. For example, in the code, "Activesheet.Range("A1")," selects A1 cell of the active sheet, but I want to select a certain range of column C (e.g. C11:C35). How would I accomplish that? In the code, I inserted Activesheet.Range("C11:C35") but the addition of new items in the dropdown menu does not occur and the list also does not get updated with the new item. What am I doing wrong? I am attaching the actual document I am working on. I highly appreciate your help. Thank you.

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

  13. #13
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    Thank you very much for your response. It worked! It was such a huge relief as I have a deadline tomorrow and I have been debugging this code for three days now.

    If it's not too much to ask, could you point me in the right direction to accomplish the following:

    The new items I add in the menu will need to be recognized as new to the list later on. Is there a way I can code the new additions to the dropdown menu as bold fonts or do something to differentiate them from the items already existing in the list? Thank you again.

    Keith.

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    Maybe this:
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.
    Last edited by incisivekeith; 11-24-2009 at 01:48 PM.

  16. #16
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    You've lost me completely.

  17. #17
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    Quote Originally Posted by JBeaucaire View Post
    You've lost me completely.
    I apologize. Okay, the following is what I need to do:

    I have 7 workbooks with 40 tabs (worksheets) each. Whatever I have been doing so far, was for one workbook. I implemented the code that we have worked on, for that one workbook. Within that workbook, in one sheet, I created several data validation dropdown lists and while I did that, I recorded the creation of those lists as macros. Now, I am trying to replicate the creation of data validation lists in other sheets within that workbook, in the same cells (and range fo cells) as the first sheet, by clicking a cell and running a respective macro. Is there a way I can do the replication of the data validation lists using macros - that I have already created for all the lists on sheet 1 - on all the other 39 sheets in Workbook 1. And after this, I have to do all this in a whole other workbook. I hope I have defined the problem clearly. Thank you very much again. I really appreciate your help.

  18. #18
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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".
    Last edited by JBeaucaire; 11-24-2009 at 02:44 PM.

  19. #19
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    I tried using Sh.Range instead of Activesheet.Range but it does not create lists on other sheets after I save the code. Am I missing something?

  20. #20
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

  21. #21
    Registered User
    Join Date
    11-20-2009
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    13

    Slvd: How to add items to a dropdown list dynamically by typing in the dropdown list

    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.
    Last edited by incisivekeith; 12-05-2009 at 07:35 PM.

  22. #22
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add items to a dropdown list dynamically by typing in the dropdown list ce

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1