CODEWITHSUNDEEP
exceloffice-2013
jarlef
jarlef

Reputation: 116

Protected sheet + hidden fornula = clear values when entering cell

I have an issue when creating a new excel workbook with protected sheets. When the user double clicks a non-locked cell (with hidden formula) the value is cleared. On unprotected sheets the value remains and the cursor is set to the end of the cell. Are there any workaround this issue?

Upvotes: 1

Views: 959

Answers (1)

Noam Brand
Noam Brand

Reputation: 346

If there are merged cells in a range the cell protection may fail, causing the described phenomenon (only hidden formulas will work without the cell protection). In this case you have 2 options:

  1. Unmerge the cells and then the protection will work.
  2. Define separate protection ranges for merged cells and unmerged cells.

For example: You try to lock and hide formulas in column "A" but cells A1 to B1 are merged. As a result column "A" will not be protected but the formulas will be hidden. If you try to edit them The cells will be erased and you will get no warning for locked cells.

Upvotes: 0

Related Questions