Double Clicking on A Locked Cell

[Ark68]

With the exception of a couple ranges of a worksheet, that worksheet's cell are locked and the sheet protected.
I have noticed though, is that if a user double clicks on a locked cell, Excel seems to assume the user has double clicked on the closest unlocked cell. My unlocked cells have doubleclick actions associated to them, so when Excel executes an indirectly double clicked cell (ie user doubleclicks a locked cell), all hell breaks loose. Code is being run when it shouldn't.

How can I avoid this happening?

 

[Joe4]

After you lock cells, you need to "Protect" the workbook/worksheet in order for it to take affect.
When you do this, it gives you a bunch of options on what you can allow users to do.
Make sure that the "Select unlocked cells" option is checked (by default, usually just the top 2 options are checked).
That will allow them to double-click and select the cell, though they won't be able to do anything with it.

 

[Ark68]

Hi Joe, thank you for your help. I already have "Select unlocked cells" checked and I'm having this behaviour. "Select locked cells" is unchecked.

 

[Fluff]

You will need to limit the code to only work on certain ranges.

 

[Joe4]

I cannot reproduce your behavior. Whenever I double-click on a locked protected cell, it selects that cell, but then gives me an error message saying:
"The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password."

 

[Fluff]

If "Select locked cells" is unchecked & I run this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox Target.Address
Cancel = True
End Sub

It gives the address of the selected cell, not the cell that was clicked.

 

[Ark68]

I think I have done that ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Stop
    Dim rng_permit As Range, rng_cdata As Range, rng_sname As Range, rng_sstart As Range, rng_send As Range
    
    With ws_gui1
        If page = 2 Then 'executes only with a date specified page, not the ActiveNet Page
            'MsgBox Target.Address
            'MsgBox Target.Row
            'MsgBox Target.Column
'
            Set tgt = Target
            'Debug.Print tgt
            tgt_r = Target.Row
            tgt_c = Target.Column
            Set rng_permit = .Range("C6:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
            Set rng_cdata = .Range("AP4:AP" & .Cells(.Rows.Count, "AP").End(xlUp).Row)
            Set rng_sname = .Range("BA4:BA" & .Cells(.Rows.Count, "BA").End(xlUp).Row)
            Set rng_sstart = .Range("BD4:BD" & .Cells(.Rows.Count, "BD").End(xlUp).Row)
            Set rng_send = .Range("BE4:BE" & .Cells(.Rows.Count, "BE").End(xlUp).Row)
            Set rng_asgmt = .Range("AM4:AM" & .Cells(.Rows.Count, "AM").End(xlUp).Row)
            
            If Not Intersect(Target, rng_permit) Is Nothing Then 'doubleclick in permit range
                MsgBox "Permit range - Row: " & tgt_r
                sel_permit
            ElseIf Not Intersect(Target, rng_cdata) Is Nothing Then 'doubleclick in coredata stats range
                MsgBox "Core Data - Row: " & tgt_r
                sel_coredata
            ElseIf Not Intersect(Target, rng_sname) Is Nothing Then 'doubleclick in staff name
                MsgBox "Staff Name - Row: " & tgt_r
                Stop
            ElseIf Not Intersect(Target, rng_sstart) Is Nothing Then 'doubleclick in staff start
                MsgBox "Staff Start - Row: " & tgt_r
                Stop
            ElseIf Not Intersect(Target, rng_asgmt) Is Nothing Then 'doubleclick in assignment
                MsgBox "Assignment - Row: " & tgt_r
                Stop
            Else 'Intersect(tgt, rng_send) Then
                MsgBox "Staff End - Row: " & tgt_r
                Stop
            End If
        Else
            MsgBox "Nothing to see here."
        End If
    End With
End Sub

Normally the cells in these ranges are locked, and then become accessible in the code associated with that selection.

So, what I am experiencing is rng_asgmt is locked. rng_sstart is unlocked. When a user double clicks a cell in rng_asgmt it triggers the code associated to a double click in the rng_sstart. In fact, if I click on any protected cell on the worksheet, it always jumps to a cell in rng_sstart.

I'm wondering if when the worksheet becomes active, it goes to the first available unlocked cell and as that cell is selected by default, that is the one that the interest target is picking up on.

 

[Ark68]

Hi Fluff, yes ... adding "Cancel = True" did correct this behaviour. I don't understand it, but it works!

Thank you all for your gesture of support.

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[Ark68]

Oh no Fluff. Now no double clicks are being picked up. Even in unlocked ranges I am getting "Nothing to see here" (with reference to my code above).