You could try to work around my code that uses BeforeDoubleClick. In following example initially the cells must be all protected and the sheet protected with pwd: 1234. The cells which may be edited are B3:C4.
An issue is that the password for the cells is the same for the sheet and that the last cell changed will not be blocked when finished.
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Answer As String
If Not Intersect(Target, Range("B3:C4")) Is Nothing Then
'Cancel = True 'to avoid entering Edit Mode
If Target.Count > 1 Then Exit Sub 'one cell at the time
Answer = InputBox("Password ?") 'ask for password
If Answer = "" Then Exit Sub
ActiveSheet.Unprotect Password:=Answer
Range("B3:C4").Locked = True
ActiveCell.Locked = False 'leave unlocked only the doubleclicked cell
ActiveSheet.Protect Password:="1234"
ActiveSheet.EnableSelection = xlNoRestrictions 'allow clicking on the cells of the range
End If
End Sub
You could add this code to block all cells when finished editing a cell:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3:C4")) Is Nothing Then
ActiveSheet.Unprotect Password:="1234"
Range("B3:C4").Locked = True
ActiveSheet.Protect Password:="1234"
ActiveSheet.EnableSelection = xlNoRestrictions
End If
End Sub
Bookmarks