| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conflict in code somewhere
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
conflict in code somewhere
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
conflict in code somewhere
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
conflict in code somewhere
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 "Jock" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
conflict in code somewhere
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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
conflict in code somewhere
OK, thanks JLG.
-- Traa Dy Liooar Jock "JLGWhiz" wrote: 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
conflict in code somewhere
Typo alert...
Put that "N" in double quotes. JLGWhiz wrote: 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" wrote in message ... 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Name Conflict | Excel Discussion (Misc queries) | |||
| Name Conflict | Excel Discussion (Misc queries) | |||
| conflict with code | Excel Programming | |||
| Conflict | Excel Programming | |||
| Name conflict | Excel Programming | |||
Linear Mode
