1

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.

1 Answer 1

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .