Selectable locked cells

[Goppi]

Does anybody know how I can leave cells selectable but not editable when I protect a worksheet?

cheers...

 

[rmikesmith]

Set the Locked property of those cells to False before protecting the worksheet.


Mike

 

[Goppi]

But than you can edit the cells - which I don't want to have.

 

[rmikesmith]

Sorry, I misunderstood your post. If the Locked property is set to True (the default) and worksheet is protected, then you can select but not edit. Am I missing something?


Mike

 

[Goppi]

Sorry, it was my fault. I left some quite important information out. the point is that the biggest part of the sheet should not be selectable - that part is locked. The part that is not locked should be selectable, but still not editable. To achieve the selection-bit I used the ActiveWorksheet.EnableSelection = xlUnlockedCells statement. So all locked cells aren't editable and selectable, the not locked cells are selectable but are also editable. But I want to have them not editable as well.

 

[rmikesmith]

I understand now. This requires a different tactic. First, change worksheet EnableSelection property to xlNoRestrictions. In the code module for the worksheet, add the following:

Dim oCell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Application.Intersect(Target, ActiveSheet.Range("B1:E1")) Is Nothing Then
    MsgBox "This cell cannot be selected"
    oCell.Select
  Else
    Set oCell = ActiveCell
  End If
End Sub

For this example, I'm treating cells B1 to E1 as the range of cells that the user can select, but not edit (the Locked property of these cells should be set to True). If the user attempts to select a cell other than those allowed they get the message and then the selection returns to the previously selected cell. In order for the oCell variable to be initialized, include the following Worksheet_Activate procedure also:

Private Sub Worksheet_Activate()
   Set oCell = ActiveSheet.Range("B1")
   oCell.Select
End Sub

This sets oCell to the first allowable cell in the range. You could also set this value in a standard module procedure (but declare oCell in that module as Public).

HTH
Mike

 

[Goppi]

hm, that would work, but I would rather like to use your idea but the other way round. Basically to have the selectable cells unlocked and to deny the user from selecting locked cells. if a user tries to change a cell to deny that by modifying the event methode in a similiar way to SelectionChange. any ideas on how I can do that? It would look better...

 

[rmikesmith]

I have tested my solution and it appears to do what you want:
[ul][li]Allow the user to select a range of cells you define while preventing selection of all other cells[/li]
[li]Prevent the user from editing cells, even those that are allowed to be selected[/li][/ul]
I don't see what difference it will make to the user as to how this is accomplished behind the scenes. Therefore I don't understand your statement

It would look better...

I don't believe there is a clean way to keep the user from editing unlocked cells (I'm referring to those cells whose Locked property is set to False). You would need to respond to user changes in the Worksheet_Change event procedure and revert to the previous value. This seems clunky to me. Much better to stop the user from making changes before they have begun (the Change event only fires after the user completes the edit).

My 2 cents

Mike