Autofilter method of range class failed - table

Question

I am trying to do this and it runs fine and I got the table filtered based on criteria but It gives me error : Autofilter Method of range class failed.

I am not sure if I am using the correct event to trigger this code:

Here is part of the code the last line is giving the error.

Private Sub ComboBox1_Change()

Application.EnableEvents=False

Dim wt As Worksheet
Dim wib As Worksheet
Dim i As Long, j As Long, frow As Long, ck As Boolean, scol As Long, ecol As Long
Dim manName As String

manName = Me.ComboBox1.Value
Set wt = Sheet3
Set wib = Sheet9   'IB Skills Sheet

wt.Activate
wt.Range("A1") = 2

If Trim(manName) = "" Then
    MsgBox "Manager Selected is Invalid"
    Exit Sub
End If

wib.Activate
wib.Range("C2").Select
wib.AutoFilterMode = False
wib.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:= _
    manName

I also tried this but I got another error:

wib.AutoFilterMode = False
frow = wib.Range("A" & Rows.Count).End(xlUp).Row
wib.Range("A1:BH:" & frow).AutoFilter Field:=3, Criteria1:=manName

What's interesting is if I put Debug.Print "Blah Blah..." just before this I get it printed twice in the Immediate window.

wib.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:= _
    manName

I don't know why it is so ? Maybe I am using wrong event handler here ? Need your assistance. Thank you.

Example:

If I use this :

wib.Activate
wib.Range("C2").Select
wib.AutoFilterMode = False
Debug.Print "Bla.."
wib.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:= _
    manName
MsgBox "Table Filtered"

I get this in the immediate window

Bla..

Bla..

I never get the Msgbox prompt because the error occurred on the line before it. But when I check the Table on the worksheet it is filtered.

Answer 1

UserForm controls events are not affected by Application.EnableEvents property

so that you must handle them appropriately

should you want to control a single UserForm events you may want to add a module level variable (say, EnableEvents) and set it back and forth to True or False in order to prevent some multiple event firing

for instance

Option Explicit

Dim EnableEvents As Boolean '<~~ declare a UserForm scoped variable to account for its event handling
                            '    -> need to add a check in every event handler sub you want to control with it

Private Sub UserForm_Initialize()
    EnableEvents = True '<~~ initialize UserForm event handling variable to True
End Sub


Private Sub ComboBox1_Change()
Dim wt As Worksheet
Dim wib As Worksheet
Dim i As Long, j As Long, frow As Long, ck As Boolean, scol As Long, ecol As Long
Dim manName As String

If EnableEvents = True Then '<~~ let it "fire" the first time

    Application.EnableEvents = False '<~~ this may not be necessary anymore

    manName = Me.ComboBox1.Value
    Set wt = Sheet3
    Set wib = Sheet9   'IB Skills Sheet

    wt.Activate
    wt.Range("A1") = 2

    If Trim(manName) = "" Then
        MsgBox "Manager Selected is Invalid"
        Exit Sub
    End If

    wib.Activate
    wib.Range("C2").Select
    wib.AutoFilterMode = False

    EnableEvents = False '<~~ set UserForm event handling variable to False so as not to have this event fired again till next setting it to True
    wib.ListObjects("Tabella1").Range.AutoFilter Field:=3, Criteria1:=manName
    EnableEvents = True '<~~ set UserForm event handling variable back to True to have this event fired normally

    Application.EnableEvents = True ''<~~ this may not be necessary anymore

End If

End Sub

---

edit: added a local event control mode

if you only want to handle a single control specific event you may want to use a static variable like follows

Option Explicit

Private Sub ComboBox1_Change()
Dim wt As Worksheet
Dim wib As Worksheet
Dim i As Long, j As Long, frow As Long, ck As Boolean, scol As Long, ecol As Long
Dim manName As String  

Static ComboBox1_Change As Boolean '<~~ "event-level" variable controlling its triggering

ComboBox1_Change = Not ComboBox1_Change '<~~ toggle the "event-level" variable -> this will have it fire alternatively (you may want to set a different behavior)
If ComboBox1_Change = True Then '<~~ it'll fire alternatively: 1st time YES, 2nd time NO, 3rd time YES...

    ... here goes your code ...     

End If

End Sub