K
KLion
1. Excel help suggests the idea of cell protection is to prevent data and
formulas in protected cells from being changed when the worksheet is
protected.
2. The fact that formatting capabilities are disabled when a worksheet is
protected further suggest that Microsoft's intent was that the formatting of
protected and unprotected cells should be protected when the worksheet is
protected.
These are also what are necessary to create a usable Excel form that allows
any user to enter information into a spreadsheet without damaging it.
Unfortunately, neither 1. nor 2. is true.
1. Unless formulas in protected cells refer to unprotected cells only using
OFFSET or other indirect references, those formulas will be changed if the
contents of the unprotected cells they refer to are moved or written over
using Cut and Paste. This is so bad that you can actually replace references
in a protected formula with a #REF! error; but in any case, once the formula
has been changed, the worksheet will no longer work properly.
2. Even though formatting is disabled when a worksheet is protected, if you
modify the contents of unprotected cells using Cut or Copy and Paste, you can
change or completely remove the formatting of unprotected cells. This is so
bad that if the unprotected input location is a group of merged cells, the
merged formatting will be removed, and the contents (depending on the type of
data) may no longer display at all, and may may the worksheet useless.
I reported these to Microsoft as bugs when I found them in Excel 5.0, but
they still exist. I have not been able to find any reference to these
problems anywhere. To me, it looks like, after over 10 years, it is still
impossible to create a form of any complexity that can't inadverntantly be
messed up or destroyed.
Does anyone know what I'm missing or doing wrong?
formulas in protected cells from being changed when the worksheet is
protected.
2. The fact that formatting capabilities are disabled when a worksheet is
protected further suggest that Microsoft's intent was that the formatting of
protected and unprotected cells should be protected when the worksheet is
protected.
These are also what are necessary to create a usable Excel form that allows
any user to enter information into a spreadsheet without damaging it.
Unfortunately, neither 1. nor 2. is true.
1. Unless formulas in protected cells refer to unprotected cells only using
OFFSET or other indirect references, those formulas will be changed if the
contents of the unprotected cells they refer to are moved or written over
using Cut and Paste. This is so bad that you can actually replace references
in a protected formula with a #REF! error; but in any case, once the formula
has been changed, the worksheet will no longer work properly.
2. Even though formatting is disabled when a worksheet is protected, if you
modify the contents of unprotected cells using Cut or Copy and Paste, you can
change or completely remove the formatting of unprotected cells. This is so
bad that if the unprotected input location is a group of merged cells, the
merged formatting will be removed, and the contents (depending on the type of
data) may no longer display at all, and may may the worksheet useless.
I reported these to Microsoft as bugs when I found them in Excel 5.0, but
they still exist. I have not been able to find any reference to these
problems anywhere. To me, it looks like, after over 10 years, it is still
impossible to create a form of any complexity that can't inadverntantly be
messed up or destroyed.
Does anyone know what I'm missing or doing wrong?