Newbie's macro doesn't appear to work!

  • Thread starter Thread starter evillen
  • Start date Start date
E

evillen

Can someone please explain why this little macro won't work?

TIA
Len
_____________

Dim Msg

Private Sub Worksheet_Change(ByVal Target As Range)
Msg = "You must NOT check your own work!"
If Target.Column <> 6 Or Target.Column <> 9 Then Exit Sub
If Target.Column = 6 And Target = Target.Offset(0, -1) Then
MsgBox (Msg)
End If
If Target.Column = 9 And Target = Target.Offset(0, -2) Then
MsgBox (Msg)
End If
End Sub
 
Len,

It is helpful if you describe what specific problem you are having with the
procedure -- e.g., what is does do that it shouldn't or what it doesn't do
that it should -- rather than simply saying that is doesn't work.

I suspect that your problem is with the line

If Target.Column <> 6 Or Target.Column <> 9 Then Exit Sub

Here, you will exit the sub regardless of what the value of Target.Column
is. If Target.Column = 6, then "Target.Column = 9" will return True, and you
will exit the sub. Similarly, if Target.Column = 9, then "Target.Column =
6" will return True and you will exit the sub. Finally, if Target.Value =
any other number, but conditionals will return True and you will exit the
sub.

You should change the "Or" to an "And". E.g.,

If Target.Column <> 6 And Target.Column <> 9 Then Exit Sub

In this case, you will exit the sub if Target.Column is any value other than
6 or 9.
 
Hi Chip

Sorry about not putting more detail - you are quite right about that
and you were also correct with your analysis of my syntax problem.

Many thanks
Len
 
Back
Top