Cannot Edit Formulas with Data Form in Excel

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

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 5.0

Keywords :
Version :
Platform :
Issue type :