lock cell when populated

English
#1

When a cell is populated can it be locked automatically

#2

The Default cell style has set the attribute Protected anyway, but cell protection only works if sheet protection also is activated. So you need first to create an unprotected cell style, and apply it to all cells expected to accept an entry. at all. This done, you can create a ConditionalFormat (CF) for the same range which sets a cell as soon as it has any content to a cell style having the Protected attribute set. The next step then is to activate sheet protection.
How the described proceeding looks and works in a sheet is demonstrated in the attached document.
ask270624LockByConditionalFormat.ods

#3

BTW, far off topic:
Something being poulated should have a population. The recently developed misuse of the word “populate” for filling data fields in forms or spreadsheet cells should be reconsidered. “Fill” is good and clear enough. Languages develop. They shouldn’t exclusively get worse.

#4

I will use fill from now on, but before you correct language please check your spelling first.

#5

Sorry. My criticism concerning languages is global while my native (and most critisized) language is German. I’m by far not perfect in correctly spelling either English or American. Of course, also my word-power and the grammar are doubtable (or even bad since I was a somehow reluctant learner 60 years ago). I’m always thankful, however, if I get specified corrections. :slight_smile:

#6

Hello,

yes - this can be done via Conditional Formatting - and requires the following steps:

Step 1: Disable protection of all cells of your sheet

  • Open styles manager View -> Styles (or F11)
  • Right click style *Default / Default Cell Style* -> Modify -> Tab: Cell Protection
  • Disable Option: [ ] Protected
  • Click OK

Step 2: Create a cell style with Protected enabled
(this cell still will be required, when defining the Conditional Formatting)

To create a new cell style

  • Open styles manager View -> Styles (or F11)
  • Click icon Styles (leftmost icon beneath word Styles)
  • Right click style *Default / Default Cell Style* -> New -> Tab: Organizer
  • Provide a name in Name field (e.g. ProtectedByContent)
  • Go to Tab: Cell Protection and set Option: [x] Protected
  • Click OK

Step 3: Define the Conditional Formatting

  • Select all cell by clicking the square at the intersection of row and column headers (all cells get selected)
  • Format -> Conditional -> Condition...
  • Check field Range shows A1:AMJ1048576
  • Condition 1 Formula is and enter NOT(ISBLANK(A1))
    Apply Style Use the cell style name defined in Step 2 (e.g. here: ProtectedByContent)
  • Click OK

Step 4: Protect Sheet and Save your Document

  • Protect your sheet using Tools -> Protect Sheet (and set your desired protection level)
  • Save your file

See the following sample file: ProtectedByContent.ods

Hope that helps.

#7

Thank you Opaque that solution worked perfectly

#8

Please do **not** use *Add Answer* if you actually don't answer a question but commenting an answer or responding to another user’s comment. Please use ***add a comment*** for that purpose. Thanks in advance …
*If the answers works for you, please consider to click the check mark (✔) next to the answer. Thanks in advance ...*

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.