Resetting Check Boxes & Advanced Filter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

documike - 31 Dec 2004 16:47 GMT

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

Reply to this Message

Ron de Bruin - 31 Dec 2004 17:31 GMT

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

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

>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.
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Dave Peterson - 31 Dec 2004 18:52 GMT

#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.

> 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
[quoted text clipped - 17 lines]
>
> Thanks

Signature

Dave Peterson

Reply to this Message