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
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