Userform to apply filter to specific sheets depending on checkbox and combobox

Question

Some help with this one will be appreciated.

The aim/idea is that I have a Userform with 2 checkboxes and a Combobox.

In the Combobox, there is a list of names which appear on 2 sheets, the 2 Checkboxes are the names of the sheets.

The user selects the name they want to find on the list and also selects which sheet they want to apply the filter to. (just one or both)

The code I have written below applies the filter perfectly when the "La Worklist" sheet is selected but doesn't do anything when the "SkillsMatrix" sheet is selected. Not sure where I'm going wrong.

Private Sub Btn_AgentSearch_Click()

'Reset sheet first
Sheets("LA Worklist").Select
ActiveSheet.AutoFilterMode = False  
Sheets("SkillsMatrix").Select
ActiveSheet.AutoFilterMode = False

'Run Filter
If Chk_LAWrkList.Value = True Then
  Sheets("LA Worklist").Select
  Range("D6").Select
  Selection.AutoFilter
  ActiveSheet.Range("$B$6:$MB$100").AutoFilter Field:=3, Criteria1:= Cbo_AgentFilter.Value
           
Else
    
End If
        
If Chk_SkillsMatrix = True Then
    Sheets("SkillsMatrix").Select
    Range("D9").Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$9:$V$51").AutoFilter Field:=3, Criteria1:= Cbo_AgentFilter.Value
        
Else
    
End If
                    
End Sub

Userform Screenshot

SkillsMatrix Screenshot

LA Worklist Sheet Screenshot

You don't need the empty `Else` blocks, and you might want to read [How to Avoid Using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Chronocidal, Jul 26 '21 at 11:38
The logic looks OK. Have you set a breakpoint and stepped through the code to debug it? https://learn.microsoft.com/en-us/office/vba/language/how-to/set-and-clear-a-breakpoint — Absinthe, Jul 26 '21 at 11:42
Are you able to display an examples of both the UserForm values (e.g. `Cbo_AgentFilter.Value`) and screenshots of the obfuscated Worksheets, in case the issues lie in either of those? — Chronocidal, Jul 26 '21 at 11:46
I have now added sceenshots, the Cbo_AgentFilter.value is a name from a named range which applies to both sheets. — Tomas Joe Gemine, Jul 26 '21 at 12:43

FaneDuru · Accepted Answer · 2021-07-26T11:51:33.060

Please, try the next code. No need to select anything...

Private Sub Btn_AgentSearch_Click()
 Dim wsLA As Worksheet, wsSk As Worksheet

 Set wsLA = Sheets("LA Worklist")
 Set wsSk = Sheets("SkillsMatrix")

 wsLA.AutoFilterMode = False
 wsSk.AutoFilterMode = False

 'Run Filter
 If Chk_LAWrkList.value = True Then
    wsLA.Range("$B$6:$MB$100").AutoFilter field:=3, Criteria1:=Cbo_AgentFilter.value
 End If
        
 If Chk_SkillsMatrix = True Then
    wsSk.Range("$B$9:$V$51").AutoFilter field:=3, Criteria1:=Cbo_AgentFilter.value
 End If
End Sub

What is Chk_SkillsMatrix? If a check box, it is good to use Chk_SkillsMatrix.value. Excel is able to guess what you need, but it is better to cultivate such a habit, I think.

Otherwise, if a boolean not declared variable, that code part will never work, the variable being False, in case of not having Option Explicit on top of the module.

Userform to apply filter to specific sheets depending on checkbox and combobox

1 Answers1