Different cell protection in the same Worksheet

7
votes

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

  • can't edit the value of A1
  • can edit the value of B1
  • can't change the formatting of B1

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

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

Additional info:

  • A solution using VBA is OK (though it looks that the Worksheet.Protect Method has the same limitations...)
  • The cell A1 (for which Edit must be prevented) can be protected against Formatting or not (it doesn't matter).
  • It should work with Excel 2003.
1
excelvba

1 Answers

2
votes

Locked doesn't address formatting as long as you don't have the allow format cells checked or activated in vba. Therefore you can simply unlock B1 and you wont be able to edit the format with just allowing users to select locked and unlocked cells enabled.

Sub protectorate()

    activesheet.Range("B1").Locked = False

    With ActiveSheet
        .Protect
    End With


End Sub

This will prevent formatting and value changes on the entire sheet except the value of B1, it will still let you edit the value of B1.