Shut off a specific macro

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
538
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

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

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
538
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
73,092
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
73,092
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,204
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top