Autofilter and checkboxes

[Westbury]

I want to use a a groups of checkboxes in conjunction with the autofilter function in Excel. I can see plenty of examples of code to set up the autofilter where the criteria are being written into a worksheet but nothing similar to my needs.

I have a group of 9 checkboxes to be used against column K and a couple more groups on other columns. In each group any one or any number of the checkboxes can be selected.

Can someone give me a steer on dealing with this variable number of criteria in VBA please?

Thanks
Geoff

 

[Westbury]

I've set up the following code to determine which checkboxes have been selected. I've alos declared the continet names as strings---perhpas they should be variants??

If ActiveSheet.OLEObjects("CheckBox" & 1).Object.value = "True" Then
            Africa = Cells(21, 10).value
    End If
    
    If ActiveSheet.OLEObjects("CheckBox" & 2).Object.value = "True" Then
       Asia = Cells(22, 10).value
     End If
    
    If ActiveSheet.OLEObjects("CheckBox" & 3).Object.value = "True" Then
       CentralAmerica = Cells(23, 10).value
     End If
    
    If ActiveSheet.OLEObjects("CheckBox" & 4).Object.value = "True" Then
       Europe = Cells(24, 10).value
     End If
     
     If ActiveSheet.OLEObjects("CheckBox" & 5).Object.value = "True" Then
       MiddleEast = Cells(25, 10).value
     End If
     
     If ActiveSheet.OLEObjects("CheckBox" & 6).Object.value = "True" Then
       NorthAmerica = Cells(26, 10).value
     End If
     
     If ActiveSheet.OLEObjects("CheckBox" & 7).Object.value = "True" Then
       Pacific = Cells(27, 10).value
     End If
     
     If ActiveSheet.OLEObjects("CheckBox" & 8).Object.value = "True" Then
       Russia = Cells(28, 10).value
     End If
     
     If ActiveSheet.OLEObjects("CheckBox" & 9).Object.value = "True" Then
       SouthAmerica = Cells(29, 10).value
     End If
  
 Worksheets("Output").Select
 
'Selection.AutoFilter
 
 Worksheets("Output").rgData.AutoFilter field:=11, Criteria1:= _
 Array(Africa, Asia, CentralAmerica, Europe, MiddleEast, NorthAmerica, Pacific, Russia, SouthAmerica), _
 Operator:=xlFilterValues

The code picks up my trial setting where I've ticked Afica. All the others are blank. so in my colmn 11 I'm expecting the auto filter to tick Africa but when I run the code I get a run-time error 438
Object doesn't support this property or method.


Help would be appreciated.

Geoff

 

[Fluff]

What is rgData?
Also does range J21:J29 hold the values you want to filter on?

 

[Westbury]

I've put rgData as a range and

Set rgData = ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion

Yes, J21:J29 [on another sheet] is a list of the continent names that I'm trying to filter on. The check boxes are in K21:K29. My logic is to determine which checkboxes are ticked. For those that are, then the string assigned to each checkbox contains the relevant name from J21:J29. e.g. stringAfrica contains the word Africa to be included in the autofilter array. Stepping through the code shows this works but it falls over at the last command shown above.

 

[Fluff]

Ok, try replacing the code you've posted with

   Dim Ary(1 To 9) As Variant
   Dim i As Long
   For i = 1 To 9
      If ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = "True" Then
         Ary(i) = Cells(i + 20, 10)
      End If
    Next i
    rgData.Range("A1").AutoFilter 11, Ary, xlFilterValues

 

[Westbury]

I've tried your suggestion and still get the same error message. But if I edit the

Worksheets("Output").Range("A1").CurrentRegion.AutoFilter field:=11, Criteria1:= _
 Array(Africa, Asia, CentralAmerica, Europe, MiddleEast, NorthAmerica, Pacific, Russia, SouthAmerica), _
 Operator:=xlFilterValues

to remove rgData and replace with Range("A1").CurrentRegion then the filter is set up but Africa doesn't appear, infact I see only the header row and blank lines below the end of the data range.

I've also made this edit and run my original code and it works..... . There seems to be an issue with using rgData

How do I add 2 more filters for columns E & F to the autofilter command? and how do I copy the filtered data to another sheet?

 

[Fluff]

You said you had

Set rgData = ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion

but now you are using

Worksheets("Output").Range("A1").CurrentRegion.

That's probably why you had the error.
What filters do you want to add?

 

[Westbury]

Yes,I had to make some edits to the worksheets. Appologies, corrected back to "data". Your code in post #5 works but I've had to replace rgData as I identified in post #6 with Range("A1").CurrentRegion. I'm using Excel 2013 if that makes any odds?

The code works to filter the data in column K by applying the selected checkboxes. I would like to add 2 more sets of checkboxes. I've placed checkboxes 10 to 13 in column N21 to N24 to refer to numeric values in cells M21 to M24 & a further 5 checkboxes in Q21 to Q 25 refering to text in P21 to P25

I've had a go at trying to copy the filtered data to another sheet with

Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy,??? , rgOutput

but the layout needs the criteria where I've put the ??? How is this done with al these filters?

Thanks for your help.
Geoff

 

[Fluff]

What columns should be filtered with the values from col M & P

 

[Westbury]

What columns should be filtered with the values from col M & P

sorry, E and F respectively