Results 1 to 4 of 4

Thread: Two codes conflict. How to resolve them

  1. #1

    Two codes conflict. How to resolve them



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    In sheet i have one code "change" to transfer data to next sheet when a condition is met.

    Code:
    Private Sub Worksheet_Change(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:
    Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 0 Then Exit Sub
    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 Sub
    Both of them conflict because of change.
    How can I modify second code to make it work.

  2. #2
    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

  3. #3
    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 by Malcolm; 2013-03-30 at 08:48 PM.

  4. #4
    Hi
    It works well.
    Thanks for your great help my friend
    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •