Shut off a specific macro

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Can you please post you code?
 

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477
Here is the private sub

Code:
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.


Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
I have the "Application.EnableEvents = False" line at the start of my macro
Are you sure about that? ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

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

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,292
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top