Shut off a specific macro

[rjplante]

I have a Private Sub macro that runs upon worksheet change. I have another macro that is trying to enter a new row of data to the bottom of the list. Whenever this macro runs, the on change macro interrupts the operation and I believe is what is causing the problem with my main macro from fully executing.

I have the "Application.EnableEvents = False" line at the start of my macro, but this is not stopping the Private Sub on this worksheet from running. Is there another way to stop this code from running?

Thanks,

Robert

 

[Fluff]

Can you please post you code?

 

[rjplante]

Here is the private sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Range("L8:L2008").ClearContents


'Return active row or column in named cells.
[SelRow] = ActiveCell.Row


Call EDIT_TEXT




End Sub


Sub EDIT_TEXT()


If ActiveCell.Row > 7 And ActiveCell.Column < 13 Then


    Range("L" & Range("Z2").Value).Value = "EDIT"


Else
    Exit Sub


End If




End Sub

Here is the code that runs from my user form.

Private Sub CommandButton1_Click()


'   ENTER new line item


Application.ScreenUpdating = False
Application.EnableEvents = True


ActiveSheet.Calculate


Dim Lrow As Long
Dim MyRow As Long
Dim DataRow As Long
Dim SearchString As String
Dim SearchRange As Range
Dim Lastrow As Long


UserForm1.Hide


'   Go to the last row plus 1 (first empty available row)


    On Error Resume Next
    Lrow = Cells.Find(what:="*", _
                    after:=Range("A1"), _
                    lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    searchdirection:=xlPrevious, _
                    MatchCase:=False).Row
    On Error GoTo 0


    MyRow = Lrow + 1
    
Sheets("Main Page").Unprotect


    Rows(Lrow).Copy
    Rows(MyRow).PasteSpecial


    Range("A" & MyRow & ":C" & MyRow).ClearContents
    Range("E" & MyRow & ":K" & MyRow).ClearContents
  


'   Transfer data to from the entry fields into the Agreements page
    'Range("A" & MyRow).Select
    
    Range("A" & MyRow).Value = Sheets("Main Page").Range("AO2").Value
    Range("B" & MyRow).Value = Sheets("Main Page").Range("AP2").Value
    Range("C" & MyRow).Value = Sheets("Main Page").Range("AQ2").Value
    
    Range("E" & MyRow & ":K" & MyRow).Value = Sheets("Main Page").Range("AR2:AX2").Value
   
    Sheets("Main Page").Range("C5").NumberFormat = "@"
    Sheets("Main Page").Range("C5") = UCase(Format(Date, "dd-mmm-yyyy"))


'   Check for company name in list
    Sheets("Data").Visible = True
    Sheets("Data").Select
    
'   Update Company Name to Data Page
    Sheets("Data").Range("AA1").Value = Sheets("Main Page").Range("AR2").Value


    SearchString = Sheets("Data").Range("AA1").Value
    Lastrow = Cells(Rows.Count, "C").End(xlUp).Row


    Set SearchRange = Range("C1:C" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        If SearchRange Is Nothing Then
    
        '   Add Company to list
            Cells(Lastrow + 1, "C").Value = SearchString
            
        End If


'   Sort Company list
    Sheets("Data").Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add KEY:=Range("C1:C5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("C1:C5000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
            
    With ActiveWindow
        .ScrollColumn = 1
        .ScrollRow = 1
    End With
    
    Sheets("Data").Range("A1").Select


    Sheets("Main Page").Activate
    With ActiveWindow
        .ScrollColumn = 1
        .ScrollRow = 8
    End With


    Sheets("Main Page").Range("AP2").Value = 5
    Sheets("Main Page").Range("AR2:AX2").ClearContents
    Sheets("Main Page").Range("BA2").ClearContents
    Sheets("Main Page").Range("BB2").ClearContents
    Sheets("Main Page").Range("BC2").Value = Year(Date)
    Sheets("Data").Range("AA1").ClearContents
        
    Call Sort_Company
    
    Sheets("Data").Visible = False
    
    ActiveWorkbook.Save
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
'Call OptimizeCode_End


End Sub

 

[Fluff]

I have the "Application.EnableEvents = False" line at the start of my macro

Are you sure about that?

 

[rjplante]

Thanks for helping me see the not so obvious.

 

[Fluff]

You're welcome & thanks for the feedback.

It's easy to miss little things like that & a fresh pair of eyes always helps.