Autofilter using VBA code getting error with condition in different cell than table?

[babaso_tawase]

I have table data like below of range a8:q20 and condition in cell E2 with combobox with dropdown value 1 to 12
Sheet name is linearity and table name is points

I used vba code

Private Sub ComboBox1_Change()
Sheet1.Unprotect Password:="a"
Dim points As Range
With Worksheets("Linearity")
Set points = .Range("E2")
With .Range("a8:q20")
.AutoFilter Field:=1, Criteria1:="<=" & points, Operator:=xlFilterValues, Visibledropdown:=False
End With
End With
Sheet1.Protect Password:="a"
End Sub


but getting error whole running code with option end or debug.
Please help in code.

Sr.no	Data1	Data2	Data3	Data4
1	abc	W3	te	Ty
2	ade		At
3	aa		Atr

 

[Alex Blakenburg]

I have logged off for the night.
What is the error message ? Is it still the protected sheet one then it is likely you have an issue with the password.
If it is a different error message what is it and what line is highlighted when you press debug ?
You might need to share your workbook via dropbox, google drive or some other sharing platform and I can have a look tomorrow.

Your initial code didn't work because the syntax for filtering a table is slightly different to standard filtering.

 

[babaso_tawase]

I have logged off for the night.
What is the error message ? Is it still the protected sheet one then it is likely you have an issue with the password.
If it is a different error message what is it and what line is highlighted when you press debug ?
You might need to share your workbook via dropbox, google drive or some other sharing platform and I can have a look tomorrow.

Your initial code didn't work because the syntax for filtering a table is slightly different to standard filtering.

When I press debug then below line highlight,

.Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False

I have given gdrive link below for file.

google drive link

 

[Alex Blakenburg]

There appears to be a 2nd issue in that the ComboBox1_Change event is calling itself, once when the ShowAllData is line is run and again when the Autofilter is applied.
There seems to be no clean way of handling this. @MARK858 has suggested something like the below:

Dim b_cbo1stPass As Boolean

Private Sub ComboBox1_DropButtonClick()

    b_cbo1stPass = True
    
End Sub

Private Sub ComboBox1_Change()
If b_cbo1stPass = True Then
    b_cbo1stPass = False
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="a"
    
    Dim critPoints As Long
    Dim tblPoints As ListObject
    
    With Worksheets("Linearity")
        critPoints = .Range("E2").Value
    End With
    
    Set tblPoints = Range("Points").ListObject
    
    With tblPoints
        If tblPoints.ShowAutoFilter = True Then
            tblPoints.AutoFilter.ShowAllData
        End If
        
        .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False
    End With
    
    Sheet1.Protect Password:="a"
    
    Application.ScreenUpdating = True
End If

End Sub

 

[babaso_tawase]

e

There appears to be a 2nd issue in that the ComboBox1_Change event is calling itself, once when the ShowAllData is line is run and again when the Autofilter is applied.
There seems to be no clean way of handling this. @MARK858 has suggested something like the below:

Dim b_cbo1stPass As Boolean

Private Sub ComboBox1_DropButtonClick()

    b_cbo1stPass = True
   
End Sub

Private Sub ComboBox1_Change()
If b_cbo1stPass = True Then
    b_cbo1stPass = False
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="a"
   
    Dim critPoints As Long
    Dim tblPoints As ListObject
   
    With Worksheets("Linearity")
        critPoints = .Range("E2").Value
    End With
   
    Set tblPoints = Range("Points").ListObject
   
    With tblPoints
        If tblPoints.ShowAutoFilter = True Then
            tblPoints.AutoFilter.ShowAllData
        End If
       
        .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False
    End With
   
    Sheet1.Protect Password:="a"
   
    Application.ScreenUpdating = True
End If

End Sub

Thanks alot. VBA working perfectly. I was struggling this from long ago. Really MrExcel platform helped me lot. Thanks again all.

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.

 

[babaso_tawase]

There appears to be a 2nd issue in that the ComboBox1_Change event is calling itself, once when the ShowAllData is line is run and again when the Autofilter is applied.
There seems to be no clean way of handling this. @MARK858 has suggested something like the below:

Dim b_cbo1stPass As Boolean

Private Sub ComboBox1_DropButtonClick()

    b_cbo1stPass = True
   
End Sub

Private Sub ComboBox1_Change()
If b_cbo1stPass = True Then
    b_cbo1stPass = False
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="a"
   
    Dim critPoints As Long
    Dim tblPoints As ListObject
   
    With Worksheets("Linearity")
        critPoints = .Range("E2").Value
    End With
   
    Set tblPoints = Range("Points").ListObject
   
    With tblPoints
        If tblPoints.ShowAutoFilter = True Then
            tblPoints.AutoFilter.ShowAllData
        End If
       
        .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False
    End With
   
    Sheet1.Protect Password:="a"
   
    Application.ScreenUpdating = True
End If

End Sub

sir can you help to modify this code for additionally filter data in second sheet named "sheet2" in table named "Points2" using same combobox used in sheet1 "Linearity"?

 

[Alex Blakenburg]

Are you saying that you want to apply the exact same filter on a Table called Points2 also to column 1 of that table ?
ie run the filter twice, once on Points and once on Points2

 

[babaso_tawase]

Are you saying that you want to apply the exact same filter on a Table called Points2 also to column 1 of that table ?
ie run the filter twice, once on Points and once on Points2

Sir, If I select number in combobox of sheet 1 then filter both table in sheet 1 and sheet2 at a time, tables are same in both sheets. Means selecting in combobox filter in sheet1 both sheet (sheet1 and sheet2) table data.

 

[Alex Blakenburg]

I haven't fully tested this, but give this try:

Private Sub ComboBox1_Change()
If b_cbo1stPass = True Then
    b_cbo1stPass = False
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="a"
    
    Dim critPoints As Long
    Dim tblPoints As ListObject
    
    With Worksheets("Linearity")
        critPoints = .Range("E2").Value
    End With
    
    Set tblPoints = Range("Points").ListObject
    
    With tblPoints
        If tblPoints.ShowAutoFilter = True Then
            tblPoints.AutoFilter.ShowAllData
        End If
        
        .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False
    End With
    
    Sheet1.Protect Password:="a"
    
    ' 2nd Table filter
    Sheet2.Unprotect Password:="a"              ' Change to correct sheet reference
    Set tblPoints = Range("Points2").ListObject
    
    With tblPoints
        If tblPoints.ShowAutoFilter = True Then
            tblPoints.AutoFilter.ShowAllData
        End If
        
        .Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints, Operator:=xlFilterValues ', Visibledropdown:=False
    End With
    
    Sheet2.Protect Password:="a"              ' Change to correct sheet reference
    
    Application.ScreenUpdating = True
End If

End Sub

 

[babaso_tawase]

I used below code , modified using Copilot (Microsoft chatgpt). Working fine. Thanks alot for helping.
Private Sub ComboBox1_Change()
If b_cbo1stPass = True Then
b_cbo1stPass = False
Application.ScreenUpdating = False

' Unprotect Sheet1
Sheet1.Unprotect Password:="a"

' Get the numeric filter value from cell E2 in the "Linearity" worksheet
Dim critPoints As Long
With Worksheets("Linearity")
critPoints = .Range("E2").Value
End With

' Filter the first table (Sheet1)
Dim tblPoints1 As ListObject
Set tblPoints1 = Sheet1.ListObjects("table1") ' Replace "table1" with your actual table name
With tblPoints1
If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
.Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints
End With

' Protect Sheet1
Sheet1.Protect Password:="a"

' Filter the second table (Sheet2)
Sheet2.Unprotect Password:="a"
Dim tblPoints2 As ListObject
Set tblPoints2 = Sheet2.ListObjects("table2") ' Replace "table2" with your actual table name
With tblPoints2
If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
.Range.AutoFilter Field:=1, Criteria1:="<=" & critPoints
End With

' Protect Sheet2
Sheet2.Protect Password:="a"

Application.ScreenUpdating = True
End If
End Sub