XL97: Error When Using Validation Drop-Down List Box

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, 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.

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

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

          a. 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.

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

          c. Click the Protection tab.

          d. Clear the "Locked" checkbox and click OK.

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

          f. 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 Drop-Down List Box Refer to a Range of Cells

When you create a validation drop-down list box, you can either input a list of values:

   One, Two, Three, Four

   A1: One
   A2: Two
   A3: Three
   A4: Four

To create a validation drop-down list box that refers to a range of cells for its 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 box, click "List". Click in the Source edit box, and then either use the mouse to select a range on the worksheet, or enter the range manually. The range in the edit box should look something like this:

         =$A$1:$A$4
   

5. Click OK.

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.

MORE INFORMATION

Microsoft Excel 97 includes a new feature called validation. 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 obtain its values from two different sources: 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 box, click "List". In the Source edit box, type a list of values, separated by commas. For example:

         One, Two, Three, Four
   

5. Click OK.

REFERENCES

For more information about validation drop-down list boxes in Microsoft Excel, click the Index tab in Microsoft Excel Help, type the following text

   data validation, drop-down