Disabling an Autofilter during the execution of a userform command and then reenabling after a particular step

[michaeltsmith93]

Hi,

I have an AutoFilter on Worksheet("Tracker"), and I'd like to disable it while this sub runs as part of a UserForm. At the moment, it autofilters the copied range and then finds the last empty row again and inputs the information from the UserForm. What I'd like it to do is input that info into the row that I've copied to the last empty row, and then reenable the auto-filter so it ends up in the right place. The autofilter code is in the second code box below, and that's housed in the sheet's code.

Private Sub OK_Click()
Dim ERow As Long
Dim ws As Worksheet
Set ws = Worksheets("Tracker")


'find first empty row
ERow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    
'check for input
If Trim(Me.ptmember.Value) = "" Or Trim(Me.StudyRole.Value) = "" Or Trim(Me.MatrixRole.Value) = "" Or Trim(Me.Department.Value) = "" Then
   MsgBox "Please fill all fields."
   Exit Sub
End If


'copy A6:AP6 to ERow
Range("A6:AP6").Copy Range("A" & ERow)


'place inputs into correct columns in ERow
With ws


    .Cells(ERow, 1).Value = Me.ptmember.Value
    .Cells(ERow, 2).Value = Me.StudyRole.Value
    .Cells(ERow, 4).Value = Me.MatrixRole.Value
    .Cells(ERow, 5).Value = Me.Department.Value
    
End With


'clear contents of input boxes
Me.ptmember.Value = ""
Me.StudyRole.Value = ""
Me.MatrixRole.Value = ""
Me.Department.Value = ""


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


   Application.ScreenUpdating = False
   
   Sheets("Tracker").AutoFilter.ApplyFilter
   
   Application.ScreenUpdating = False
End Sub

 

[Kamolga]

'To cancel
application.EnableEvents=False

'your code


'To put it back
application.enableevents=true

 

[michaeltsmith93]

Thank you so much!

 

[michaeltsmith93]

Should those two lines go immediately following the Private Sub line and before the End Sub line?

 

[Kamolga]

Should those two lines go immediately following the Private Sub line and before the End Sub line?

Yes, I usually start with screenupdating though...does not really matter. You can also do it around the specific part of the code but I tend to start all my sub with those two and put back at the very end as well

 

[michaeltsmith93]

Okay, thank you so much. Unfortunately, it's not executing the autofilter until I change the contents of any cell on the sheet. Additionally, it's maintaining the "Copy" state on that row A6:AP6, which I imagine is part of the problem. I've tried to get around this by putting Application.Volatile at the top, as well as adding a Range(...).Select at the very end, but neither has the intended effect. I've added the updated code here.

Private Sub OK_Click()


Application.Volatile


Application.EnableEvents = False


Dim ERow As Long
Dim ws As Worksheet
Set ws = Worksheets("Tracker")


'find first empty row
ERow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    
'check for input
If Trim(Me.ptmember.Value) = "" Or Trim(Me.StudyRole.Value) = "" Or Trim(Me.MatrixRole.Value) = "" Or Trim(Me.Department.Value) = "" Then
   MsgBox "Please fill all fields."
   Exit Sub
End If


'copy A6:AP6 to ERow
[COLOR=#ff0000]Range("A6:AP6").Copy[/COLOR]
[COLOR=#ff0000]Range("A" & ERow).PasteSpecial xlPasteAll[/COLOR]


'place inputs into correct columns in ERow
With ws


    .Cells(ERow, 1).Value = Me.ptmember.Value
    .Cells(ERow, 2).Value = Me.StudyRole.Value
    .Cells(ERow, 4).Value = Me.MatrixRole.Value
    .Cells(ERow, 5).Value = Left(Me.Department.Value, 3)
    
End With


'clear contents of input boxes
Me.ptmember.Value = ""
Me.StudyRole.Value = ""
Me.MatrixRole.Value = ""
Me.Department.Value = ""


Unload Me


Application.EnableEvents = True


End Sub