Why doesn't locking a cell in a protected worksheet in Excel prevent selection?

Question

I'm working on a protected Excel spreadsheet and ran into a problem with cells that I've programmatically locked that are still selectable. I can't click on them directly to select them, but if I select the cell to the lower right of a locked cell and then shift-click the one to the upper left, all 9 cells (including the 'locked' one) are selected. I can also click-drag to select the 9 cells. I'm using the following VBA code to protect and set the selection criteria:

Worksheets("Sheet1").Protect UserInterfaceOnly:=True
Worksheets("Sheet1").EnableSelection = xlUnlockedCells

I've tried the two commands in the reverse order and get the same results.

I've also used the Excel cell formatting / protection menus to do the same thing and get the same results.

Am I missing something obvious or is there some other programmatic way to mark a cell so that it can't be selected later on?

Answer 1

I think you'll need to create some code in the Worksheet_SelectionChange event that checks whether your protected cells are included in the new selection and, if so, selects some other cell (perhaps the next unprotected one to the right or below).

I haven't reviewed the code, but a cursory search for "Worksheet_SelectionChange protected cells selection" brought up http://www.vbaexpress.com/kb/getarticle.php?kb_id=383.

Answer 2

You not missing anything, this is simply Excel maintaining the shape of the selection in preference to the protection status of the cells.

The cells are still protected, but can be copied.

Using the Worksheet_SelectionChange event will allow you to run code to redirect the selection of protected cells to another cell. This code needs to be stored in the sheet you are trying to protect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Selectedrange As Range
    Dim ProtectedRange As Range
    Dim ws As Worksheet

    Application.EnableEvents = False

    Set ProtectedRange = ActiveSheet.Range("A1:A10")

    Set Selectedrange = Intersect(Target, ProtectedRange)

    If Selectedrange Is Nothing Then
        Exit Sub
    Else
        ActiveSheet.Range("B1").Select
    End If

    Application.EnableEvents = True

End Sub

If you just want to stop the user copying out the formulas then just add a line to your code to set 'Formula Hidden' to true. If the protected cells are copied, only the values will be copied.

Worksheets("Sheet1").Range("A1:A10").Locked = True
Worksheets("Sheet1").Range("A1:A10").FormulaHidden = True
Worksheets("Sheet1").Protect UserInterfaceOnly:=True, Password:="123"
Worksheets("Sheet1").EnableSelection = xlUnlockedCells

An alternate is to move your protected data into another sheet and set it visibilty to 'very hidden', so that it is no longer visible through the UI. The values in the 'very hidden' sheet can still be accessed via formula.

Worksheets("Sheet2").Visible = xlVeryHidden