Have you ever ran into a scenario why you want to limit the possible values a cell can have but at the same time the range of values that cell can take is changing dynamically?
Scenario: You have a dedicated sheet for entering data into your Excel Workbook. One of the fields is best represented as a drop down list via default Excel Data Validation feature. You decide to use the default data validation and everything is working perfectly well until your drop down list grows to an unimaginable size. You now end up spending more time scrolling down that list than you do entering other information.
Real World Example: Company ABC uses Excel for entering Sales information. There is a dedicated worksheet called Record Sale in which the sales people are required to enter details such as Customer, Date, Amount, etc. Instead of allowing users to enter any random Customer name, Company ABC chose to create a single sheet of Customers and forced users to choose the Customer from that sheet! In the beginning, Company ABC use the default Excel Data Validation feature. This worked perfectly well however as the number of customers increased, sales people complained that they spent too much time scrolling through the list of customers!
I wanted my solution to provide the same validation functionality found in default Data Validation but at the same time also provide auto complete to users. (Excel File: http://1drv.ms/ZBTaEt)
- Lock the cell from users. This would prevent them from making any changes to the cell directly!
- Create a basic UserForm which would be the interface that the user will have to use to make changes to our target cell. I added a combo box and a few buttons (OK and Cancel). If the user clicks on Cancel, I just hide my UserForm. If the user clicks on OK, I perform data validation on the value entered in the combo box against the range of valid values and if the value is valid, then I change the value of my target cell to the value user has chosen!
- Show UserForm whenever focus is on the target cell. This is achieved through Worksheet_SelectionChange method. Whenever the Target.Cells.Row = target cell row and Target.Cells.Column = target cell column, I have to show the user a user form which allows them to choose the value for that cell!
When I first came across a similar problem like this, I thought that this should be a fairly common problem for Excel users, so I was really surprised to see the solutions people have shared online were far from the ideal solution. The following is a list of solutions I tried out and what I didn’t like about them.
- Adding a formula to my Customer cell: “=OFFSET(Sheet1!$A$1,0,0,MATCH(“*”,Sheet1!$A$1:$A$300,-1),1))”. While this formula fills in a value by default, it confuses users because they forget they actually have to change the default value to their target value. Furthermore, users have grown used to seeing multiple options as they are entering the name of the company but this solution only shows one value at any time.
- Using a regular combo box. Before I tried out this approach, I was convinced that this solution will do the trick! Why else would anyone want to use a regular combo box?! It turns out I was very wrong. While this solution doesn’t provide any auto-complete functionality, it allows developers to customize the look and feel of the drop down list which can be very useful!