Geek Answers Handbook

Various cell protections in the same worksheet

Inside an Excel worksheet, I would like to protect cell (A1) from Editing and another cell (B1) from formatting. This means that the user:

  • cannot change the value of A1
  • can edit the value of B1
  • cannot change formatting B1

The Protect Sheet option allows you to protect all locked cells of the entire sheet, so A1 and B1 will have the same level of protection when locked (either against formatting or editing).

Is there a way to set individual protections for a specific cell or range, or at least get the same behavior?

Additional Information:

  • The solution using VBA is fine (although it seems like the Worksheet.Protect method has the same limitations ...)
  • Cell A1 (for which editing must be disabled) can be protected from formatting or not (it does not matter).
  • It should work with Excel 2003.
+7
source share
1 answer

Blocked does not access formatting until allow cells are checked or activated in vba. Therefore, you can simply unlock B1 and you will not be able to edit the format, allowing users to select locked and unlocked cells.

Sub protectorate() activesheet.Range("B1").Locked = False With ActiveSheet .Protect End With End Sub

This will prevent formatting and changing the values ​​of the entire sheet, except for the value of B1, it will still allow you to edit the value of B1.

+2
source

More articles:


All Articles