MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding Entry to a dropdown


Posted by Todd Bennett on January 23, 2002 10:06 AM

I have a dropdown being fed the list from another page. If the user doesn't choose from the list but enters a new value, I want to add it to the list so when they go to the drop down, the new value is there.
Could use any help I can get. Thanks in advance.

Todd


Posted by Terry on January 23, 2002 12:06 PM

I assume you mean you have a value in the RowSource property pointing to the list for a Combobox object. If the value keyed into the combobox text area is not in the values stored in the RowSource then you want to add it. There are two approaches. If you have a common exit point for your form such as a Stop or Done button, before exiting the form you can unload the Combox back into the cells on the sheet that is pointed to by RowSource. The issue is you have to leave enough room in this group of cells for the maximum number of values you want to store. Also you have to make sure the user always exits via this Done button in order to save the contents of the combobox. First however, you have to use the Combobox Change event which will get control when someone changes the text portion of the Combobox. Upon gaining control, you have to use the Listindex property to determine if an item was selected from the list or keyed in. The Listindex should be set to -1 if the value did not come from the list. In this case you can just take the value and use the Additem method to the combobox to add it to the list. This implies that you do not use the RowSource property but at Form startup time you load each item from the cells using the Additem method to the combobox. Then when they click the Done button the routine discussed earlier can replace the cells with the updated list. Another alternative is to use the Rowsource property and when you detect the value was keyed using the Listindex of -1, you can switch to the sheet containing the cells for the Rowsource and add the value to one of the rows/columns in the on this sheet so that Rowsource will reflect it. You can either leave empty rows in the Rowsource cells for additional entries or you can change the Rowsource value with updated cell ranges as you increase the range.

Posted by Dave Gibson on January 25, 2002 7:29 AM

If you're using data validation to provide the drop down, I found some VBA code at Ozgrid.com to validate a value entered into the dropdown, if it's not there, a prompt allows the user to add it to the validation list. It worked great, and was very simple.