Validating user input in excel vba
I'll explain how you can create in-cell drop-down lists, along with how to "future proof" the Data Validation list so that the feature won't require maintenance if you add additional items in the future.This technique also resolves an annoying problem in Excel 2007 where data validation lists cannot be placed on other worksheets. Before we look at Data Validation, let's first establish our list.
Let's say we want the user to choose a type of fruit.On the second worksheet of a blank workbook, create a list, such as shown in Figure 1.Working in an Excel spreadsheet can be somewhat like the Wild West – unless other provisions are made, users can enter any value in any cell.One way you can restrict users to a predefined set of values is by way of Excel's Data Validation feature.The data for the drop down list will come from the range “=A1: A6” in the sheet “Sheet1”.
Note you must change the highlighted parts based on the location of your source and the location for your drop down list: In order to modify, add, insert and remove items from a drop down list created using data validation, you would have to follow 2 steps.