Target of a double click event can be wrong when on a protected sheet. Looking for workaround

[UWMmakow]

I Googled for a while and couldn't find this issue.

I have a protected sheet with a BeforeDoubleClick event as follows:

If Not Intersect(Target, Range("M1")) Is Nothing Then
     frmMain.Show
End If

M1 is unlocked, along with about a dozen other cells. The ~dozen cells are for user entry, and M1 brings up a form with access to some tools when double clicked. All other cells are locked and not selectable.

If M1 is selected, and the user double clicks over any of the locked cells, the form opens. This must be because M1 is the target, being that it is still the currently selected cell. Even though the double click didn't actually occur on M1.

Of course I want to avoid this. Can anyone think of a work around? Thanks.

 

[Osvaldo Palmeiro]

Hi.
Try allowing access to locked cells as you protect the sheet.

Also, put a Cancel=True command:

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

 

[UWMmakow]

Wow, for some reason I thought if you let users access the locked cells, they could also edit them. But I never tested it! Thanks for the heads up.

What does Cancel = True do?

 

[Osvaldo Palmeiro]

Cancel=True gets rid of the Edit Mode of the cell that was double clicked and, in this case, it prevents the alert message after DoubleClick on a locked cell.

 

[UWMmakow]

Cool, thanks!