Resetting Check Boxes & Advanced Filter

[documike]

I created a form that uses Check Boxes for the end user to select certain
items which in turn generates a TRUE or FALSE output into another cell. I
then used ADVANCED FILTER to select the associated rows and copy them to
another page in the WORKBOOK.

I am trying to figure out a couple of things:
1. Is there a way to automatically reset all the check boxes to FALSE status
(basically un-check all the check boxes)? I tried recording a macro and it
didn't work.
2. What is the easiest way to automate Data/Advanced Filter & Copy To?
Record a macro?

What I am trying to do is this:
I have about 20 rows of data with a Check Box at the left of each row.
The end user selects the rows to use with the Check Boxes
I want to envoke Advanced Filter to pick the rows to Copy to another
location (filtering on the TRUE or FALSE Check Boxes)
I want the end user to be able to reset the Check Boxes and go through the
same process the next time they use the tool.

Thanks

 

[Ron de Bruin]

Hi

You can use a loop

Sub test2()
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Object.Value = False
End If
Next
End Sub


Or if you use the Forms CheckBoxes

ActiveSheet.CheckBoxes.Value = False


See my Add-in for the Copy part
http://www.rondebruin.nl/easyfilter.htm

 

[Dave Peterson]

#1. Another way is to change all those linked cells to false:

You could either loop through that range, or if they're all touching:

with worksheets("sheet1")
.range("a1:A20").value = false
end with

#2. That seems like a nice way to start. But when you start recording your
macro, make sure you're on the sheet that will receive the data.