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,
--
Traa Dy Liooar
Jock
"Patrick Molloy" wrote:
> 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" wrote:
>
> > 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.
> > --
> > Traa Dy Liooar
> >
> > Jock
|
Similar Threads
