Getting Data Entry Right the First Time

 < Day Day Up > 



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.



 < Day Day Up > 



Microsoft Excel 2003 Programming Inside Out
Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))
ISBN: 0735619859
EAN: 2147483647
Year: 2006
Pages: 161

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net