Disable Double Clicking of Locked Cells on Protected Sheets

[Ark68]

I have a protected worksheet (being used as a form) in which all the user interactive cells are locked. Only unlocked cells are selectable. I use VBA to unlock them at the appropriate time so the user can enter appropriate information based on the values of previously entered data (often times triggering the unlocking of cells for user entry).

During my worksheet "initialization" - first use - the sheet is protected and only cells C3, E3, G3 and M3 is unlocked for user entry. These cells each have unique list validations. M3 has a 'default' message, which provides only a message for the user, and is not a valid option (not included in the validation list). These 4 cells all have worksheet change event code associated with them.

I also have a range of cells, I13:K19, that have BeforeDoubleClick events associated with them. During initialization, this range is locked as well.

The issue I am having is that if the user double clicks on any cell of the protected worksheet, it triggers the worksheet double click event reserved for range I13:K19, which is locked. Since the cell that the user selected is locked, Excel considers the next unlocked cell as being the cell double clicked. (for example, M3 is unlocked. If the user double clicks Z26, the diagnostic message I coded shows an address of M3. The result is a blinking cursor at the end of the default text in M3. If I click on M3's dropdown button, the cell's validation error message pops up because the default value isn't in the list of available selections. If I press OK, worksheet change code for M3 is triggered and results in errors (as the default value passed is invalid)

How can I disable double clicking of cells except only those I want, when I want?

 

[Ark68]

I think I found the solution ... the inclusion of Cancel.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'MsgBox Target.Address
    If Not Intersect(Target, Range("I13:K19")) Is Nothing Then
         MsgBox "Cell double clicked: " & Target.Address
    Else
        Cancel = True
    End If
End Sub

 

[Ark68]

I think I found the solution ... the inclusion of Cancel.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'MsgBox Target.Address
    If Not Intersect(Target, Range("I13:K19")) Is Nothing Then
         MsgBox "Cell double clicked: " & Target.Address
    Else
        Cancel = True
    End If
End Sub

Nope ... still doing unpredictable things.

 

[Ark68]

Nope ... still doing unpredictable things.

Turns out that after the double click code executed, I had to send the focus of the cursor somewhere out of site via code. Without this, everytime the user manually left the double clicked selected cell, focus would go to the next unlocked cell and cause a bunch of problems.