PDA

View Full Version : [SOLVED] VBA Before Double-Click Event executing when not desirable



papaeight
03-12-2018, 03:11 AM
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
03-12-2018, 05:42 AM
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
03-12-2018, 06:22 AM
>Target.Count > 1


It's always false?

SamT
03-12-2018, 06:53 AM
If (Target.Count > 1 OR Intersect(Target,

papaeight
03-12-2018, 06:57 AM
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
03-13-2018, 12:53 AM
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.