XL: Locking Fields in a Data Form

Last reviewed: February 2, 1998
Article ID: Q100936

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh versions 2.2, 3.0 ,4.0
Microsoft Excel 98 Macintosh Edition

SUMMARY

By default, all cells except those containing formulas can be edited in a data form. However, if you want to keep cells that do not contain formulas from being edited, you can protect these cells on the worksheet. Protecting these cells will prevent the edit box for the protected column from being displayed in the data form.

This situation applies both to built-in data forms and custom data forms.

MORE INFORMATION

Example

If you wanted to prevent someone from changing the salary field in the following database

    A              B              C                D
   ------------------------------------------------------
   Name            Title          Hire Date        Salary
   John            Manager        6/8/92           $50,000

lock column D on the spreadsheet and protect the document. When you protect the document this way, no one can to add new data or change existing data in this column until you unlock the column.

To lock the column in versions of Microsoft Excel that are earlier than version 5.0, do the following:

Select Columns A, B, and C.
From the Format menu, choose Cell Protection, clear the Locked check box, and choose OK.
From the Options menu, choose Protect Document, and choose OK.

To lock the column in Microsoft Excel 5.0 and later, do the following:

Select Columns A, B, and C.
From the Format menu, choose Cells.
Select the Protection tab, clear the Locked check box, and choose OK.
From the Tools menu, choose Protection. Select Protect Sheet, then choose OK.

NOTE: You will not be able to add new records or delete existing records while in protect mode. This procedure is only useful for editing existing records.

Additional query words: howto
Version : WINDOWS:2.0,3.0,4.0,5.0; MACINTOSH:2.2,3.0,4.0,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.