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 SubThat 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.
1 Answer
The best solution I came up with was to leave all cells locked except the selectable editable cells and protect the worksheet with Select locked cells and Select unlocked cells checked.
Then to make the desired unselectable cells be unselected if the user selects any I added the following VBA code to the worksheet:
Private PreviousSelection As Excel.Range
Private PreviousActiveCell As Excel.Range
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Const SelectableUneditableRangeAddress As String = "B2:C3" Dim SelectableUneditableRange As Excel.Range Dim UsedRange As Excel.Range Dim Cell As Excel.Range Dim SelectRange As Excel.Range Dim ActivatedCell As Excel.Range Set SelectableUneditableRange = Target.Worksheet.Range(SelectableUneditableRangeAddress) Set UsedRange = Excel.Intersect(Target, Excel.Union(SelectableUneditableRange, Target.Worksheet.UsedRange)) For Each Cell In UsedRange If Not Excel.Intersect(SelectableUneditableRange, Cell) Is Nothing Or Not Cell.Locked Then If SelectRange Is Nothing Then Set SelectRange = Cell Else Set SelectRange = Excel.Union(SelectRange, Cell) End If End If Next Cell Excel.Application.EnableEvents = False If SelectRange Is Nothing Then If PreviousSelection Is Nothing Then Set PreviousSelection = Excel.Selection End If PreviousSelection.Select If PreviousActiveCell Is Nothing Then Set PreviousActiveCell = Excel.ActiveCell End If PreviousActiveCell.Activate Set PreviousActiveCell = Excel.ActiveCell Else Set ActivatedCell = Excel.ActiveCell SelectRange.Select Set PreviousSelection = SelectRange If Excel.Intersect(SelectableUneditableRange, ActivatedCell) Is Nothing And ActivatedCell.Locked Then Set PreviousActiveCell = PreviousSelection.Cells(1, 1) Else ActivatedCell.Activate Set PreviousActiveCell = ActivatedCell End If End If Excel.Application.EnableEvents = True
End Sub
