Checkmark function

  • Thread starter Thread starter Tom Bock
  • Start date Start date
T

Tom Bock

I came acroos this very useful function in the MS-Newsgroups. This allow
to make to add "checkmarks" into column B by right-clicking any cell in
column B.

********** My questions is... how do I modify it to also include column E?
********

I tried to change "If Target.Column <> 2 Then Exit Sub" to "If
Target.Column <> 2 OR If Target.Column <> 5 Then Exit Sub", but that doesn't
work.

I also tried to to rename the function by changing the function name to
"Worksheet_BeforeRightClick_B" and then duplicating the function with an
"_E" extension. That didn't work either.

Any ideas? BTW, I don't know why there is an intersect reference to column
N.

Thanks,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&


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

If Target.Column <> 2 Then Exit Sub
If Intersect(Target, Me.Range("b:n")) Is Nothing Then Exit Sub
If Intersect(Target, Me.Range("B:N")) Is Nothing And Intersect(Target,
Me.Range("N:N")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=char(252)"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If

Cancel = True 'stop the rightclick menu

errHandler:
Application.EnableEvents = True

End Sub
 
Frank:

Thanks for the quick reply... that works for me....

Why do I use the AND instead of the OR? Any idea why the range to goes
"N"?

Thanks,
Tom
 
Hi tom
this If statement checks if any of both columns is affected by a
change. Only if BOTH (-> usage of AND) columns are not affected the
procedure is ended
 
Because you are checking for Not aEqual nd it will always Not Equal 2 OR Not
Equal 5, even if you are in columns 2 or 5, but it will only be Not Equal 2
AND Not Equal 2 if you are in any columns other than 2 or 5.

The code can be simplified to ( and it uses OR!)

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

If Target.Column = 2 Or Target.Column = 5 Then

On Error GoTo errHandler:
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=char(252)"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If

End If

Cancel = True 'stop the rightclick menu

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
THANKS!!! I appreciate the feedback.

Tom


Bob Phillips said:
Because you are checking for Not aEqual nd it will always Not Equal 2 OR Not
Equal 5, even if you are in columns 2 or 5, but it will only be Not Equal 2
AND Not Equal 2 if you are in any columns other than 2 or 5.

The code can be simplified to ( and it uses OR!)

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

If Target.Column = 2 Or Target.Column = 5 Then

On Error GoTo errHandler:
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Formula = "=char(252)"
Target.Font.Name = "Wingdings"
Else
Target.ClearContents
End If

End If

Cancel = True 'stop the rightclick menu

errHandler:
Application.EnableEvents = True

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top