VBA Before Double-Click Event executing when not desirable

[papaeight]

Hi,

First time posting to this forum. I hope this is an easy solution!!!

I am using the Marlett checkbox trick to make a tick appear and disappear on double-click across two ranges (myChecks and ckBoxes). This part works flawlessly currently. However the code is also executing when double-clicking happens almost anywhere else. I have checked these other columns are not in either of the two named ranges. I also have a Change Event written for the Ckboxes range which clears content from some cells in the range depending on what cell was double-clicked. I have noticed that the event is not triggered when double-clicking a merged cell. I take it that 'target' means the active cell so thought that the Intersect code would prevent the event being triggered when the active cell is not in my two ranges however it does still. I could code again for all the other places that a user could be allowed to double-click (i.e. for editing a cell) but it seems like overkill. Please help with an easier solution!!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect "***"
    If (Target.Count > 1 And Intersect(Target, Range("myChecks")) Is Nothing) Then Exit Sub
    
    Target.Font.Name = "marlett"
    If Target.Value <> "a" Then
        Target.Value = "a"
        Cancel = True
        ActiveSheet.Protect "***"
    Exit Sub
    End If
    
    If Target.Value = "a" Then
        Target.ClearContents
        Cancel = True
        ActiveSheet.Protect "***"
        Exit Sub
        End If
    
    If (Target.Count > 1 And Intersect(Target, Range("Ckboxes")) Is Nothing) Then Exit Sub
    
    Target.Font.Name = "marlett"
    If Target.Value <> "a" Then
        Target.Value = "a"
        Cancel = True
        ActiveSheet.Protect "***"
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.ClearContents
        Cancel = True
        ActiveSheet.Protect "***"
        Exit Sub
    End If
    
    ActiveSheet.Protect "***"
End Sub

[mancubus]

welcome to the forum.

perhaps:

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

    ActiveSheet.Unprotect "***"
    
    If Target.Count > 1 Then Exit Sub
    
    If Intersect(Target, Range("myChecks")) Is Nothing And Intersect(Target, Range("Ckboxes")) Is Nothing Then Exit Sub
    
    With Target
        .Font.Name = "marlett"
        If .Value <> "a" Then
            Cancel = True
            .Value = "a"
            ActiveSheet.Protect "***"
            Exit Sub
        End If
    
        If .Value = "a" Then
            Cancel = True
            .ClearContents
            ActiveSheet.Protect "***"
            Exit Sub
        End If
    End With
    
End Sub

[mana]

>Target.Count > 1


It's always false?

[SamT]

If (Target.Count > 1 OR Intersect(Target,

[papaeight]

Mancubus. You're a star! Works exactly as required. Of course I see now that my original 'And' conditions were allowing the two If to always return False so the subsequent code would implement both when desired and when not desired. Also I see you making the same point Mana. Thanks SamT that would work too as an OR clause.

[mancubus]

you are welcome.
thanks.

in events, i prefer conditions be written on different statements (to prevent confusion) and end the code immediately, if any of the conditions are not met, rather than resuming it if one condition is met.