Double Click Cell

[D Willett]

Hi ( Hope this isn't a Dooohh moment again)

I'm unlocking a cell via vba on a protected sheet:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("H2:H23")) Is Nothing Then
    Dim PW As String
    PW = "myPassword"
    On Error GoTo ProtectMe
    With ActiveSheet
        .Unprotect PW
        .Cells(1, 1).Locked = False
        .Cells(1, 1).Clear
        .Cells(1, 1).Locked = True
ProtectMe:
        ActiveSheet.Protect PW
        
    End With
    
    End If
End Sub

However, when I dbl Click the cell I see the formula instead of the cleared value, I can overwrite the formula (which is what I want) and the sheet is protected after my edit.
How do I clear the cell value so when it is dbl clicked the user see's only a cursor in the cell?

Cheers

[HansV]

Cells(1, 1) is cell A1, not the cell being double-clicked.
Clearing a cell also turns Locked to True again, so you should use ClearContents instead of Clear.
If you lock a cell, you won't be able to edit it. So the target should remain unlocked if you want the user to be able to edit it.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim PW As String
    PW = "myPassword"
    If Not Intersect(Target, Range("H2:H23")) Is Nothing Then
        On Error GoTo ProtectMe
        Me.Unprotect PW
        Target.Locked = False
        Target.ClearContents
ProtectMe:
        Me.Protect PW
    End If
End Sub

[D Willett]

Yes I see, and it works.
However in my Range of H2:H23 I am finding once dbl clicked the whole range is unlocked as opposed to the single cell which is any of the cells in Range H2:H23.
The single cell within that range will be quite happy being unlocked but can we restrict this to only the single cell?

[HansV]

In my test, only the cell that you double-click is unlocked (and remains unlocked), the rest is still locked...

[D Willett]

Ok great, I will double check the protection properties on this.

As ever, thank you for the help Hans.

Regards