Autofilter and checkboxes

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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??

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,816
Office Version
  1. 365
Platform
  1. Windows
What is rgData?
Also does range J21:J29 hold the values you want to filter on?
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,816
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, try replacing the code you've posted with
VBA Code:
   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

Board Regular
Joined
Jun 7, 2009
Messages
136
I've tried your suggestion and still get the same error message. But if I edit the

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,816
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You said you had
Rich (BB code):
Set rgData = ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion
but now you are using
Rich (BB code):
Worksheets("Output").Range("A1").CurrentRegion.
That's probably why you had the error.
What filters do you want to add?
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
136
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

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,816
Office Version
  1. 365
Platform
  1. Windows
What columns should be filtered with the values from col M & P
 

Watch MrExcel Video

Forum statistics

Threads
1,122,310
Messages
5,595,401
Members
413,990
Latest member
Kher83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top