XL2000: Error When Using Validation Drop-Down List Box

ID: Q211520

The information in this article applies to:
  • Microsoft Excel 2000

SYMPTOMS

In Microsoft Excel 2000, 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.
and 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 box does not refer to a range of cells for its values, but instead contains them directly

    -and-


  • The cell containing the validation drop-down list is locked.

    -and-


  • The worksheet containing the cell is protected.



WORKAROUND

To work around this problem, you can 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.

Method 1: Unlock the Cell Containing the Drop-Down List Box

  1. If the worksheet in question is currently protected, point to Protection on the Tools menu , and click Unprotect Sheet. If you are prompted for a password, type it and click OK.


  2. Select the cell that contains the validation drop-down list. On the Format menu, click Cells.


  3. Click the Protection tab.


  4. Click to clear the Locked check box and click OK.


  5. Repeat steps 2-4 for any other cells in your worksheet that contain validation drop-down boxes.


  6. After the cells have been unlocked, restore the protection on your worksheet. Point to Protection on the Tools menu, and click Protect Sheet (type a password if necessary).


Method 2: Make Your Drop-Down List Box Refer to a Range of Cells

  1. Select the cell that is to contain a validation drop-down list box.


  2. On the Data menu, click Validation.


  3. Click the Settings tab.


  4. In the Allow list, click List. Click in the Source box, and then either use the mouse to select a range on the worksheet, or type the range manually,for example: A1:B5+C7.


  5. Click OK.


The new validation drop-down 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.


MORE INFORMATION

You can use validation to make sure that users only enter specific values, or specific types of values, into cells in Microsoft Excel.

One validation option allows you to attach a drop-down list box to a cell when the cell is selected. If you use this option, users can only enter values in the cell that are contained within the list box. The list box can refer to a range of cells for its values, or it can contain them directly.

To create a validation drop-down list box that directly contains its own values, follow these steps:

  1. Select the cell that is to contain a validation drop-down list box.


  2. On the Data menu, click Validation.


  3. Click the Settings tab.


  4. In the Allow list, click List. In the Source box, type a list of values, separated by commas, for example:


    5. One, Two, Three, Four
  5. Click OK.


When you select the cell, the drop-down list box appears, and you can select a value from the list. You cannot manually type a value into the cell.


REFERENCES

For more information about validation drop-down list boxes in Microsoft Excel, click Microsoft Excel Help on the Help menu, type Specify the valid entries for cells in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : xlui xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug


Last Reviewed: November 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.