Getting Data Entry Right the First Time

---

Typing data into a worksheet or form is one of the most tedious activities known to humanity. If you've ever typed ISBNs into a database eight hours a day and five days a week for two months, you can begin to get an appreciation for what data entry clerks go through. Columns of numbers transpose in front of your eyes, books you've already entered find their way onto your 'to do' pile, and you change hands to avoid the worst effects of repetitive stress disorders. Add in the potential for typographical errors, and you can understand why electronic data collections are so notoriously inaccurate.

You can help catch data entry errors at the source by setting validation rules for cells in your worksheets. As the name implies, a validation rule is a criterion that cell data must meet in order to ensure that only meaningful information is added to your data collection. You get to choose whether the invalid data is accepted as input or whether the user has to re-type the data before being allowed to go on to the next cell. You can also specify whether to allow users entering data to leave cells blank.

The key to creating validation rules for your cells is, as you probably guessed, the Range object's Validation property. The Validation property, which returns a Validation object, gives you the tools to set the validation rules and notification styles for your cell ranges. Table 8-4 lists the Validation object's useful properties and methods.

Table 8-4: The Properties and Methods of the Validation Object

Attribute	Description
Properties
AlertStyle	This property can be set to one of three Excel constants: xlValidAlertInformation (which displays an information box), xlValidAlertStop (which displays a stop box), xlValidAlertWarning (which displays a warning box).
ErrorMessage	This property contains the user-defined message that appears in the alert box after a user enters invalid data.
ErrorTitle	This property contains the user-defined value that appears on the title bar of the alert box that appears after a user enters invalid data.
Formula1	This property contains the first (and possibly the only) value used in a criterion (for example, between 5 and 10 or less than 10).
Formula2	This property contains the second of two values used in a criterion (for example, between 5 and 10).
IgnoreBlank	Setting this Boolean property to True allows a user to leave a cell blank.
InCellDropdown	This property determines whether the cell contains a down arrow with a list of values from which a user must pick.
InputMessage	This property contains the message that appears in the input box into which a user enters cell data.
InputTitle	This property contains the value that appears on the title bar of the input box into which a user enters cell data.
Operator	This property contains the operator of a criterion, which may be one of the following Excel constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, and xlNotEqual.
ShowError	A Boolean property that, when set to True, causes Excel to display a message indicating the user entered invalid data.
ShowInput	A Boolean property that, when set to True, causes Excel to display the contents of the InputMessage property whenever the cell is activated.
Type	A property that determines the type of value you will be validating. The available data types are xlValidateCustom, xlValidateDate, xlValidateDecimal, xlValidateInputOnly, xlValidateList, xlValidateTextLength, xlValidateTime, and xlValidateWholeNumber.
Value	A Boolean property set to True if all validation rules are satisfied, or False if at least one rule is not satisfied.
Method
Add	A method to create a validation rule for a range of cells. Table 8-5 gives you more information on which parameters you need to set.
Delete	A method that deletes a Validation object.
Modify	A method that changes the validation rules for a range of cells. The Modify method uses the same parameter rules listed in Table 8-5.

Which parameters of the Add and Modify methods you use depends on the type of value you want to validate. The corresponding values for each validation type are shown in Table 8-5.

Table 8-5: The List of Parameters Used in an Add or Modify Statement

Validation Type	Parameters
xlValidateCustom	Formula1 is required, whereas Formula2 is never used. (Any value assigned to Formula2 is ignored.) Formula1 must contain an expression that evaluates to True when data entry is valid and False when data entry is invalid.
xlInputOnly	AlertStyle, Formula1, or Formula2 are used.
xlValidateList	Formula1 is required, but Formula2 is never used. (Any value assigned to Formula2 is ignored.) Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list.
xlValidateWholeNumber, xlValidateDate, xlValidateDecimal, xlValidateTextLength, or xlValidateTime	One of either Formula1 or Formula2 must be specified, or both may be specified.

If you wanted to set validation criteria for 600 cells in the H column of a worksheet, you could use the following code to do so.

With Range("H6, H606").Validation
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlLessEqual, Formula1:="5000"
    .InputTitle = "Credit Limit"
    .ErrorTitle = "Credit Limit Too High"
    .InputMessage = "Enter the customer's credit limit."
    .ErrorMessage = "The credit limit must be less than $5,000."
End With

In this chapter, you've learned how to refer to and manipulate ranges and individual cells in your worksheets. The fundamental concept to remember is that, while you can refer to individual cells within a range, even single cells are called using the Range object (for example, Range("A16"). Once you've specified the range you want to work with, you can use the range's values in formulas, define a name to allow more streamlined and user friendly references to the range, and validate the data entered into the range.

---