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?

 

[Fluff]

Do you mean you want to allow people to double-click a locked cell

 

[Ark68]

No, just the unlocked range(s). In post 7 I try to illustrate what is happening and what I suspect may be happening. When the user is clicking on a locked cell (it doesn't select ie no border), the code interprets "a" cell in the closest unlocked range. The user would only select a locked cell if they were trying to access a range that hasn't been made accessible yet, or they were goofing around. But when they do that, the code for the unintentional selection is run.

With the added "Cancel = True" at the beginning of the BEFOREDOUBLECLICK event, that prevents that, but also prevents the code from executing properly in the ranges that are selectable. Even in ranges where the cells are unlocked in the defined accessible ranges, the intersect address isn't being recognized properly.

 

[Fluff]

Putting cancel =true at the top of the sub, should not affect the way the code runs. However with the undeclared variables & variables with no value, it's very difficult to say. No to mention the other macros the code is running.

 

[Ark68]

Hmmm. Is there anything on that note that I can provide Fluff that may help isolate the problem? I'm still tinkering around with things hoping it might have been just an isolated thing.

 

[Fluff]

I would need the workbook to offer any further help, can you share it?

 

[Ark68]

I would in an instant, but it might be difficult because it relies on additional supported workbooks. I can share those as well, but to redact the sensitive data may take a bit. Let me play around a bit more and if you are willing I can spend some time to clean it up and make it shareable. I don't want to burden you.

 

[Fluff]

If it's relying on other workbooks as well, then I'm afraid I won't have the time to sort it all out.

 

[Ark68]

Thanks for offering though Fluff. Is I can't figure it out, I'll just have to simply ask people not go crazy with the double clicking.

 

[Peter_SSs]

I can certainly reproduce the behaviour you describe. I'm not sure why your users would be trying to double-click on locked cells though, but could you implement something like this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim Resp As VbMsgBoxResult
  
  Resp = MsgBox("Code will run for double-click on " & Target.Address(0, 0) & vbLf & "Click OK to proceed, Cancel to abort.", vbOKCancel)
  
  If Resp = vbYes Then
    Cancel = True
    
    ' The rest of your code goes here
  
  End If
End Sub

 

[Ark68]

Thank you Peter.
I found another solution after I discovered the actual events leading up to the problem. I have it solved, but probably not in the most graceful manner.