On worksheet change macro control

[rjplante]

I have a macro (Macro A listed below) on a worksheet page VBA code window. Macro A will work in conjunction with conditional formatting and a defined Name which is a cell to capture the row I am on, to color the range of cells corresponding to the row I have selected. I also have a second macro (Macro B) in a module folder that turns on an autofilter and then filters based on a specific criteria. At the end of Macro A it turns on the protection of the sheet. Macro B unprotects the sheet at the beginning and then a range of data is selected. Because the range is selected MAcro A executes and then protects the sheet so that the rest of Macro B cannot run and I get an error that says I cannot turn on the autofilter with the page protected.

My question is this: How can I suspend the operation of Macro A during the execution of Macro B, and the rest of my macros for that matter?


Macro A in "Main Page" code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Sheets("Main Page").Unprotect


Range("L8:L5008").ClearContents


Range("A8:K5008").Locked = True


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


Sheets("Main Page").Protect


Call EDIT_TEXT


End Sub

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Sub EDIT_TEXT()


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


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


Else
    Exit Sub


End If


End Sub

Thanks for the help,

Robert

 

[Fluff]

You're Edit_Text as shown will not call the SelectionChange event.
What you need to do is call the Edit_text code before you reprotect the sheet.

 

[rjplante]

I made that change, but it doesn't affect the issue I am facing. The code below is the one that will fail, along with others. It fails on the line below:

ActiveSheet.Range("$A$7:$K" & Lastrow).AutoFilter Field:=4, Criteria1:="Active"

Here is the code:

    Application.ScreenUpdating = False
    
    Sheets("Main Page").Unprotect
    
    Dim Lastrow As Long


'   Define last row of data
    Sheets("Main Page").Select
    Range("A100000").Select 'Row number is hopefully large enough to include all data
    Selection.End(xlUp).Select
    Lastrow = Selection.Row


'   if autofilter is on, show all the data
    Sheets("Main Page").Select
    If ActiveSheet.AutoFilterMode Then
        Range("A7:K7").Select
        Selection.AutoFilter
        Selection.AutoFilter
    End If
  
    ActiveSheet.Range("$A$7:$K" & Lastrow).AutoFilter Field:=4, Criteria1:="Active"


    Application.Goto Sheets("All Agreements").Range("A8"), True
    
    Sheets("Main Page").Protect
    
    Application.ScreenUpdating = True

I hope this helps.

 

[Fluff]

The easiest way round the problem is to add

Application.EnableEvents = False

to the start of the code &

Application.EnableEvents = True

to the end.
The better way would to be get rid of the .Select as you do not need to select anything.

   Application.ScreenUpdating = False
   Dim Lastrow As Long
   With Sheets("Main Page")
      .Unprotect
      '   Define last row of data
      Lastrow = Range("A" & Rows.Count).End(xlUp).Row
      '   if autofilter is on, show all the data
      If .AutoFilterMode Then .AutoFilterMode = False
   
      .Range("$A$7:$K" & Lastrow).AutoFilter Field:=4, Criteria1:="Active"
      Application.Goto Sheets("All Agreements").Range("A8"), True
   
      .Protect
   End With
   
   Application.ScreenUpdating = True