Two codes conflict. How to resolve them

F

Falcon

New member
Joined
Mar 25, 2013
Messages
9
Reaction score
0
Points
0
Hi,
In sheet i have one code "change" to transfer data to next sheet when a condition is met.

Code: 
Private Sub Worksheet_[COLOR=#FF0000]Change[/COLOR](ByVal Target As Range)
Sheets("Closed Flts").Unprotect "abcde"
  If Target.Column = 16 And Target.Cells.Count = 1 Then
    Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Target.EntireRow.Delete Shift:=xlUp
    Sheets("Closed Flts").Protect "abcde"
    End If
End Sub

Same sheet I have another "change" code to initiate emails via activation of code in module 12.

Code: 
[COLOR=#333333][I]Private Sub Worksheet_[/I][/COLOR][COLOR=#FF0000][I]Change[/I][/COLOR][COLOR=#333333][I](ByVal Target As Range) If Target.Cells.Count = 0 Then Exit Sub[/I][/COLOR]
[COLOR=#333333][I]If Not Application.Intersect(Range("column12"), Target) Is Nothing Then[/I][/COLOR]
[COLOR=#333333][I]If Target.Value = "Overdue" Then[/I][/COLOR]
[COLOR=#333333][I]Call Mail_small_Text_Outlook[/I][/COLOR]
[COLOR=#333333][I]End If[/I][/COLOR]
[COLOR=#333333][I]End If[/I][/COLOR]
[COLOR=#333333][I]End Sub[/I][/COLOR]

Both of them conflict because of change.
How can I modify second code to make it work.
 
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub 'Delete if not required
On Error GoTo Exits
Application.EnableEvents = False


'Your Code

Exits:
Application.EnableEvents = True
End Sub
 
With a little more thought,
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    On Error GoTo Exits
    Application.EnableEvents = False
    If Target.Column = 16 Then
        Sheets("Closed Flts").Unprotect "abcde"
        Target.EntireRow.Copy Destination:=Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete Shift:=xlUp
        Sheets("Closed Flts").Protect "abcde"
    Else
        If Not Application.Intersect(Range("column12"), Target) Is Nothing Then
            If Target.Value = "Overdue" Then
                Call Mail_small_Text_Outlook
            End If
        End If
    End If
Exits:
    Application.EnableEvents = True
End Sub
 
Last edited:
Hi
It works well.
Thanks for your great help my friend
Regards
 
You must log in or register to reply here.
Back
Top