Cannot Edit Formulas with Data Form in Excel

 Last reviewed: November 3, 1994
Article ID: Q71881
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
  • Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

When using a data form to edit a database, Microsoft Excel will not allow you to edit any items that were entered into the database in the ="<text>" format. The entry will be displayed in the data form, but cannot be edited. This is also true for custom data forms.

Examples

  • Zip codes or other numbers that need leading zeros and were entered as ="02345"
  • Part numbers or building numbers entered as ="3-5" (so that the entry is not calculated as a date).

MORE INFORMATION

Microsoft Excel assumes that the field is a calculated field (because of the equal sign) and will display the item, but does not allow you to edit the entry.

It is also possible that, if Alternate Navigation Keys has been checked (under Workspace from the Options menu), the item might have been entered as '02345 (equivalent to ="02345") to enter the item as text. If the item was this way, Microsoft Excel will display the item in a data form and allow editing of this field. Editing the field does not work properly, however. If you edit it by entering '02346, Microsoft Excel will display the ' in the cell. If you edit it by entering 02346, Microsoft Excel will drop the leading zero in the cell.

Workaround

The best workaround is to use a custom dialog box. There is no way to properly edit these types of entries in a data form (custom or default).

In the example that uses zip codes, the best workaround is to enter the number without the leading zero and use the Number option from the Format menu to display the value correctly in the cell. For example, choose Number from the Format menu and type "00000" (without the quotation marks) in the Format box.

REFERENCES

"User's Guide," version 5.0, pages 370-375 "User's Guide 1," version 4.0, pages 314-325 "User's Guide 2," version 4.0, pages 263-282 "User's Guide," version 3.0, pages 356-368, 624-641 "Functions and Macros," version 2.x, pages 204-218 "Reference Guide," version 2.x, pages 126-129, 167-177

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 5.0 5.00

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: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.