CODEWITHSUNDEEP
excelvbaautofilter
Tomas Joe Gemine
Tomas Joe Gemine

Reputation: 81

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

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

enter image description here

SkillsMatrix Screenshot

enter image description here

LA Worklist Sheet Screenshot

enter image description here

Upvotes: 0

Views: 292

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

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.

Upvotes: 3

Related Questions