Just curious (an Excel VBA question)

Is it possible to have a userform on an excel workbook add information into the code itself

Here is what I am looking at. I have a workbook that has a user form. In this user form is a dropdown combo box that has account numbers in it. What I want to know is is it possible to allow a user to add information into the combo box (such as a new account number that will be used often) and have it so that it is listed the next time the user uses the workbook If so, how could I accomplish this

Thanks

Tiger

Answer this question

Just curious (an Excel VBA question)

adrian-

Hello Tiger,

In Excel you can base the values shown in a combo box on a range in a sheet. The RowSource property of a combo and list box can be set to a range, "Sheet1!A1:A10", if the user changes any value in that range the combo updates. The only thing is if there are any blank cells they are still listed. That might be ok but if it's not you need to work out the range and set the RowSource in code when the form loads. You might need to work out the range if the user adds new items to the list as a static range limits the number of inputs/choices.
SuriyaPrakash

Combo boxes are much more flexible than that. You can add items to them in VBA from an array or any other list, based on any parameters during operation of your procedure.

In addition, if you set the .Style property of the listbox to 0 and the .MatchEntry to 2, you can allow the user to type a new item. If you monitor the Exit event of the combo box you should be able to determine what this entry was and incorporate it into the program execution.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Just curious (an Excel VBA question)

Answer this question