In Excel cells are usually selectable and editable.
I can format some cells to be unlocked and then protect the worksheet with only the option to select unprotected cells.
That way the locked cells that weren't unlocked can't be selected or edited by the user and the ones unprotected can still be selected and editable.
My question is how can I also have cells that are selectable but not editable. A read-only state where the information can be seen, selected, copied, but not modified.
If I protect the worksheet with the option to select protected cells I get what I want but then I can't have any unselectable cells as well.
To summarize I want to have unselectable (thus uneditable) cells, selectable uneditable cells and selectable editable cells.
Preference for a solution without needing the use of VBA but if not possible then the most simple the better.
My attempted failed solution:
Protected the worksheet with the desired unselectable cells locked and the selectable cells unlocked with only the protection option Select unlocked cells
checked.
To make the cells (in this case B2:C3
) seem uneditable I added to the worksheet the VBA code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const SelectableUneditableRangeAddress As String = "B2:C3"
If Not Excel.Intersect(Target.Worksheet.Range(SelectableUneditableRangeAddress), Target) Is Nothing Then
Excel.Application.EnableEvents = False
Excel.Application.Undo
Excel.Application.EnableEvents = True
End If
End Sub
That way it undoes any changes (right after being applied) if it affects any of the selectable uneditable cells.
Other than not being ideal it also allows cutting and pasting the uneditable cells.