XL98: Error When Using Validation Drop-Down List Box (178869)
The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q178869 SYMPTOMS
In Microsoft Excel 98 Macintosh Edition, when you select a value in a
validation drop-down list box, you may receive the following error message:
The cell or chart you are trying to change is protected and therefore
read-only.
You are unable to select a value in the list.
CAUSE
This will occur if all of the following conditions are true:
- The validation drop-down list does not refer to a range of cells
for its values, but instead contains the values directly.
-and-
- The cell containing the validation drop-down list is locked.
-and-
- The worksheet containing the cell is protected.
WORKAROUND
There are two possible workarounds for this problem: you can either unlock
the cell that contains the validation drop-down list box, or you can set
the validation drop-down list so that it refers to a range of cells, rather
than directly containing its own values.
Method 1: Unlock the Cell Containing the Drop-Down List Box
To unlock the cell containing the drop-down list box, follow these steps:
- If the worksheet in question is currently protected, click the
Tools menu, point to Protection, and click Unprotect Sheet. If you are
prompted for a password, enter it and click OK.
- Select the cell that contains the validation drop-down list.
On the Format menu, click Cells.
- Select the Protection tab.
- Clear the Locked check box and click OK.
- Repeat steps 2-4 for any other cells in your worksheet that
contain validation drop-down boxes.
- Once all necessary cells have been unlocked, restore the protection on
your worksheet. Point to Protection on the Tools menu, and click Protect
Sheet (enter a password if necessary).
Method 2: Make Your List Refer to a Range of Cells
When you create a validation list box, you can either input a list of
values
or, you can set the list box to refer to a range of cells for its values:
A1: One
A2: Two
A3: Three
A4: Four
If you create a validation list using this second method, you will avoid
problems when you use the list on a protected worksheet.
To create a validation list that refers to a range of cells for its values,
follow these steps:
- Select the cell that is to contain a validation drop-down list box.
- On the Data menu, click Validation.
- Click the Settings tab.
- In the Allow box, click "List." Click in the Source box, and then either
use the mouse to select a range on the worksheet, or enter the range
manually. The resulting entry in the Source box should resemble the
following:
- Click OK.
The new validation list box will work correctly, even if the worksheet is
protected.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem
and will post new information here in the Microsoft Knowledge Base as it
becomes available.
REFERENCES
For more information about validation lists in Microsoft Excel, click the
Index tab in Microsoft Excel Help, type the following text
data validation, adding restrictions
and click Show Topics to go to the "Specify the valid entries for cells"
help topic.
Modification Type: | Minor | Last Reviewed: | 1/28/2005 |
---|
Keywords: | kbprb KB178869 |
---|
|