Double click event in protected sheet to trigger color change and value insertion

Hey crew,

Need your help. I have this worksheet with some code in it already. Kindly refer to it.

The objective is that any time a cell in the ranges I have defined is double clicked, the cell that was double clicked will turn blue, and the cell in the the corresponding row in column D returns the score rating in target column row 8.

Example, if I double click on H13. This cell (H13) will turn blue, all other cells in range E11:L14 will clear color fill formatting, and D10 will return "4" (because column H row 8 has a 4). Similarly, if I double click on I17, I17 will turn blue, all others in E17:L18 will clear color, and D17 will return a "5" (I8).

Sheet1 is protected. Passwords are stored in worksheet pwdsheet which is xlveryhidden.

The problem is that if Sheet1 is unprotected the code works. Once it runs the first time, the sheet will protect itself and double clicking will not work. It doesn't work if I close and reopen either. I'm an amateur coder, so can you please clean up my code?

Thanks

Ron

Try this...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pswdSheet2 As String
 
'store password to protect Sheet2, in Sheet4.Cells("B2")
pswdSheet2 = Sheet2.Cells(2, 2)

    Sheet1.Unprotect Password:=pswdSheet2

    If Not Intersect(Target, Range("E11:L14")) Is Nothing Then
        Cancel = True
        Range("E11:L14").Interior.ColorIndex = xlNone
        Target.Interior.Color = RGB(141, 180, 226)
        Cells(10, "D").Value = Cells(8, Target.Column).Value
    ElseIf Not Intersect(Target, Range("E17:L18")) Is Nothing Then
        Cancel = True
        Range("E17:L18").Interior.ColorIndex = xlNone
        Target.Interior.Color = RGB(141, 180, 226)
        Cells(10, "D").Value = Cells(8, Target.Column).Value
    End If
    
    Sheet1.Protect Password:=pswdSheet2, UserInterFaceOnly:=True
    
End Sub

Hi Sir,

Still not working

Nothing happens when I double click. It works if Sheet1 is unlocked, but after that the sheet locks itself with the password in worksheet "pwdsheet B2" and nothing happens when I double click.

Ron

I had to add .EnableSelection property to the lock. It's working now. Did I do it right?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim pswdSheet2 As String
 
'store password to protect Sheet2, in Sheet4.Cells("B2")
pswdSheet2 = Sheet2.Cells(2, 2)

    Sheet1.Unprotect Password:=pswdSheet2
    ActiveSheet.EnableSelection = xlNoRestrictions

    If Not Intersect(Target, Range("E11:L14")) Is Nothing Then
        Cancel = True
        Range("E11:L14").Interior.ColorIndex = xlNone
        Target.Interior.Color = RGB(141, 180, 226)
        Cells(10, "D").Value = Cells(8, Target.Column).Value
    ElseIf Not Intersect(Target, Range("E17:L18")) Is Nothing Then
        Cancel = True
        Range("E17:L18").Interior.ColorIndex = xlNone
        Target.Interior.Color = RGB(141, 180, 226)
        Cells(16, "D").Value = Cells(8, Target.Column).Value
    End If
    
    Sheet1.Protect Password:=pswdSheet2, UserInterFaceOnly:=True
    ActiveSheet.EnableSelection = xlNoRestrictions
    
End Sub

Glad you figured it out.

You don't need the first EnableSelection when it unprotects the sheet. It doesn't hurt having it though.