Search code examples
excelvbaspreadsheet-protection

Select specific locked cells, leaving other cells 'unselectable'

In Excel VBA, is there a way to select specific locked cells, while leaving other cells 'unselectable'?

I have an excel sheet, which is protected. I would like to give the user the ability to select specific locked cells within a sheet while at the same time not allowing them to select other locked cells within the same sheet. Basically I have a small, cell-based calendar, in which I would like them to be able to select specific dates (locked cells) on this small range, however there is no need for them to select any other locked cells within the sheet. Excels Sheet Protection only seems to allow a sheet-wide "Select Lock Cells". If there is a way to accomplish this through VBA I would love to hear about it. I did look over a lot of other posts before posting here.

Solution

  • When you protect a worksheet, the behaviour in each cell is defined by the Locked property. Sample code:

    ActiveSheet.Unprotect
ActiveSheet.Range("A1").Locked = False
ActiveSheet.Range("A2").Locked = True
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlNoRestrictions 'All the cells can be selected

    With this code, users can edit cell A1 but not cell A2. Bear in mind that, by default, the Locked property is set to True.

    CLARIFICATION

    Protection is applied to the whole worksheet. At the cell level, all what you can do is relying on the Locked property. What you cannot get is different behaviours for different (Locked/Unlocked) ranges.