Possible UserForm to Enter Customer Names

[brad9705]

I’m trying to simply my process to enter customers into my worksheet. I’m not sure if Excel is capable of this, so I thought I would ask for help. At this point, my thoughts are just conceptual.

I would like to have a basic UserForm with a list of customers. I would like to open the UserForm, select my customers for this order, then have the UserForm populate selected customer (in alphabet order) starting in cell D188 and going up to D263 based on the number of customer selected. I can have up-to 75-customers per worksheet. My VBA skills are limited (and that may be an overstatement), so any help or suggestions is greatly appreciated.

UserForm.jpg

I am currently using Data Validation with a Defined Name dropdown list within the range. However, there are some limitation I’ve found with Data Validation (e.g., will not auto complete as I start to type to find matching customer(s), the dropdown list only shows 8-customers, font is extremely small and cannot be changed). It time consuming to scroll through the list with only 8-customers with very small text to select each customer one at a time. So, I looking for other options.

I’m not hung up on the CheckBox or OptionButton list, it appears it would be quicker to select my customer all at once and let the UserForm populate selected customer into my worksheet (assuming that’s possible).

If you there’s an earlier or more feasible approach, I’m certainly looking for suggestions.

I do have a question and/or concern. I occasionally add a new customer. By using the UserForm with a CheckBox or OptionButton, if I add customer(s) will I have to rearrange my form to insert the customer in the appropriate alphabet sequence or can my CheckBoxes be tied to a Defined Name dynamic list and as I make changes to my list, it would update my UserForm automatically? If I would have to adjust the entire UserForm (e.g., insert customer and move others), this may become burdensome and difficult to manage...

Thank you for your help.

[MarvinP]

Hi brad and welcome to the forum,

There is an old tool called "Data Form" that you can make active and it will do what I think you want. It is very hard to create your own VBA userform. It is very easy to use the build in one. You will need to customize your toolbar and put it in your Quick Access Toolbar (QAT). Then simply put your cursor in any table and click the new Icon.

Read:
http://www.addintools.com/documents/...data-form.html
http://spreadsheets.about.com/od/dat..._data_form.htm

[AlphaFrog]

There is a method to overlay a Combobox on top of Data Validation DropDown lists. This can give you features like autocomplete and longer preview lists (more than 8), and different fonts.

Data Validation Combo box (link to guide)
To overcome the limitations of a data validation drop down list, you can add a combo box to your worksheet, and use programming to make it appear in cells that contain a data validation list.


If you don't know VBA or object oriented programming, making your own Userform will be a significant challenge.