Protected Formula Error

[Al]

This is a repost from last year that drew no response, and I’m still looking
for a solution.

I’m using Excel 2003 and I have a worksheet set up for user data entry in
which all formula cells are normally locked and the worksheet password
protected to guard against the user overwriting them. When the workbook is
opened a macro sets the password for UserInterFaceOnly, since there is
another macro that changes formatting of the formula cells based on data
entry events. There are only two formulas used repeatedly with changes in
relative references. This all works as expected, but now I have a problem
when I want to modify the formulas.

I unprotect the worksheet, and if I try to edit or overwrite a formula I get
a dialog box that says “Cell contains protected formula.†I can delete the
formula but I can’t edit it. If I delete it, I can’t make a new entry, but I
can copy and paste anything into the cell. If I copy and paste the formula
to another cell, I still can’t edit that. But if I copy it from the formula
bar and then paste it to any cell other than one of the formula cells, I can
edit it. Then I can copy and paste the edited cell back to the original
location and it’s still editable. However, if I do a formula bar copy and
paste back to the original location, any edits are retained, but I can’t do
any further editing.

What could I have set that would cause this?

 

[Gary''s Student]

1. Un-protect both the workbook & worksheet
2. Un-lock all cells
3. Disable all event macros
4. Tools > Options > Edit > check Edit directly in cell

 

[Al]

1. Workbook and worksheet unprotected.
2. Entire worksheet unlocked.
3. All event macros commented out.
4. Edit directly in cell checked off.

Still can't edit.
Saved file that way and reopened it. Still can't edit.
Blew away the rest of the workbook. Still...

Any other thoughts?

 

[JLGWhiz]

It appears that there is either a Workbook_Open or Worksheet_Activate event
or some other event code somewhere that is re-setting the protection. Check
all of the code modules that could contain event code to see if any have
automated protection that runs on the open or activate event.

 

[Al]

OK I took the next step and blew away all the macros. Nothing left but the
worksheet with my problem. Still can't edit the thing.

Blew away the entire worksheet except for one formula and the cells it
refers to, and I can't edit it.

I just don't understand what could cause the combination of things I can and
can't do.

 

[JLGWhiz]

If you find out, be sure to post back. This sounds interesting.

 

[Al]

Eureka!

The problem is that Custom data validation was turned on (with no formula
entered). Turn off data validation and the problem goes away. That leaves
me with another puzzle. Using the menu commands, you can’t set Custom
validation without entering a formula, and I certainly didn’t do it in a
macro. Since my application was built from an inherited workbook, anything
is possible. The smart tag flyover message on these cells that says, “This
cell contains a formula and is not locked to protect it from being changed
inadvertently†is a wonderful piece of half-truth that helps obscure the
issue. Apparently the smart tags never heard of data validation.