A single cell (or even an entire column) can be given a data
validation setting so that, when selected, a dropdown list appears with
a finite list the possible entries for that range.
To create a dropdown list, restricting entries in a range to those values, follow these steps:
- Select the range that is to contain the validation dropdown
listbox. This may be either a single cell or a larger range,
such as an entire column.
- From the Data menu, choose Validation.
- On the Settings tab, in the Allow dropdown list,
select List.
- The Source box can refer to a range of cells for its
values, or it can contain them directly.
- ... a direct list :
You may type the source list directly in the Source box.
In this example, the list is simply days of the week, separated
by commas. Notice there are no quotes around the text.
- ... a referenced list :
Click in the Source box, and then either type a range
reference manually [ preceded by an =
(equals) sign ] or use the mouse to
select a range on the same worksheet that has the source list
of possible entries.
If a user tries to manually enter anything other than these values,
a stop message appears and will not allow the cell to keep the
invalid entry. The only options for the user will be to
Retry or Cancel.
It will be possible to make valid entries (i.e., MON, TUE, WED, etc.)
manually in a cell to which this validation setting has been applied;
a user is not required to actually select these values from the list.
Some Possible "Gotchas"
- Validation does not look at what is already in the cell.
- It is not case sensitive: WED, Wed, or wed,
are all treated the same.
- It does not look at entries made through VBA procedures.
- If users copy or autofill data in restricted cells, error
messages don't appear, and incorrect data may be placed in the
cells.
If a range is pasted over this way, it's validation properties
are removed.
Locking the range and protecting the worksheet help prevent this
problem.
- An error will occur when selecting a validation dropdown if all
of the following conditions are present:
- the cell containing the validation dropdown is locked;
- the worksheet is protected;
- the source values for the validation dropdown is a direct list.
... Possible workarounds are:
- unlock the range containing the validation dropdown list
- set the validation dropdown list to refer to a range for its
source values, instead of a direct list.
It helps as much as anything is to employ this feature in ways where
the user is apt to enter data directly. Properly applied, validation
dropdown lists can be used to enable ease and consistancy of data entry.
Know an even better way?
Let me know.
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
