conflict in code somewhere

[Jock]

Hi,
The following code works in a Worksheet_Change (ByVal Target As Range) event:

' Displays message box when "C" is selected in H
Dim Res3 As VbMsgBoxResult
If Not Intersect(Target, Me.Range("H4:H4000")) Is Nothing Then
With Target
If Target.Value = "C" Then
If Target.Offset(, -1) = "" Then
Application.EnableEvents = False
Res3 = MsgBox("No account has been selected!", vbOKOnly, "Charging of
accounts")
Target.Value = vbNullString
Else
Res3 = MsgBox("Is this item to be charged to an account?", vbYesNo,
"Charging of accounts")
If Res3 = vbNo Then
Target.Value = vbNullString
Else
UserForm7.Show
End If
Application.EnableEvents = True
End If
End If

When another function is added to the same Worksheet_Change code, the second
one (below) doesn't work.

However, if the first one is removed, it works fine:

' when date entered in "K", checks "H"
Dim Res4 As VbMsgBoxResult
Const Myrange3 = "K4:K4000"
If Not Intersect(Target, Me.Range("K4:K4000")) Is Nothing Then
Exit Sub
End If
If IsDate(Target) And Target.Offset(, -3) = "" Or Target.Offset(, -3)
= "N" Then
Application.EnableEvents = False
Res4 = MsgBox("Cannot issue claim without payment!")
Target.ClearContents
Application.EnableEvents = True
End If


The first code above is currently at the end of about 4 pages of other code
in Sheet!1 all of which works ok until the second one is added.
I have three other subs to add but until I can figure out why they don't
work, when added in, I am stuck.


Can I have a second Worksheet_Change event to put these into or can they be
placed in ThisWorkbook or in a module?
Help!
Thanks in advance.

 

[Patrick Molloy]

you use
WITH target
there's ne end with and you don't use any of Target's properties.
you dim a const but don't use it.

what error messages do you get
put
OPTION EXPLICIT
at the top of the module, then from the menu select DEBUG/COMPILE

you can only have one event handler for a given event. Ideally, the event
code shoudl be quite short, or it should call procedures in a general Module
- this enable other sheet events to use the same code etc etc

 

[Jock]

Hi Patrick,
I don't get any error messages - that's half my problem. If I did, I could
wrestle with it.
The 'end with' is there, I missed it when copying the code though.
Rather than use Target, is there another option?
tbh, I'm a bit confused as I have a number of workbooks which do different
things and I have put this one together using code from the other
spreadsheets and from posts on this forum.
Unfortunately, I don't understand enough to sort it out!
Thanks,

 

[JLGWhiz]

I would write this:

If IsDate(Target) And Target.Offset(, -3) = "" Or Target.Offset(, -3)
= "N" Then

Like this:

If IsDate(Target) And Target.Offset(, -3) = "" Then
If Target.Offset(, -3) = "N" Then
'rest of statement
End If
End If

 

[JLGWhiz]

Disregard that. Try this:

If Target.Offset(, 3) = "" Or Target.Offset(, 3) = N Then
If IsDate(Target) Then
'Rest of statement
End If
End If

 

[Jock]

OK, thanks JLG.
--
Traa Dy Liooar

Jock

Disregard that. Try this:

If Target.Offset(, 3) = "" Or Target.Offset(, 3) = N Then
If IsDate(Target) Then
'Rest of statement
End If
End If

 

[Dave Peterson]

Typo alert...

Put that "N" in double quotes.

Disregard that. Try this:

If Target.Offset(, 3) = "" Or Target.Offset(, 3) = N Then
If IsDate(Target) Then
'Rest of statement
End If
End If