Protected Formula Error
 

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

Im 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 cant edit it. If I delete it, I cant 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 cant 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 its still editable. However, if I do a formula bar copy and
paste back to the original location, any edits are retained, but I cant do
any further editing.

What could I have set that would cause this?

--
Al C

Protected Formula Error
 

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
--
Gary''s Student - gsnu200792


"Al" wrote:

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

Im 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 cant edit it. If I delete it, I cant 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 cant 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 its still editable. However, if I do a formula bar copy and
paste back to the original location, any edits are retained, but I cant do
any further editing.

What could I have set that would cause this?

--
Al C

Protected Formula Error
 

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?

--
Al C


"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200792


"Al" wrote:

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

Im 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 cant edit it. If I delete it, I cant 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 cant 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 its still editable. However, if I do a formula bar copy and
paste back to the original location, any edits are retained, but I cant do
any further editing.

What could I have set that would cause this?

--
Al C

Protected Formula Error
 

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 cant set Custom
validation without entering a formula, and I certainly didnt 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.

--
Al C