Using a Macro to clear out check box seletions
 

I have a sheet built that I have added a "Clear" button. I was easily able to
record a macro that will clear specific data entry cells, however, it will
not remove the check marks from the various check boxes I have put into the
sheet for users to select. Can anyone help me with this? I would like the
clear button to clear out the cells and check boxes at the same time, giving
the user a new, clean page to begin data entry in for the next batch.

Hi Mel

You can use a macro like this for control toolbox controls

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

For forms checkboxes use this

ActiveSheet.CheckBoxes.Value = False

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



"Mel" wrote in message ...
I have a sheet built that I have added a "Clear" button. I was easily able to
record a macro that will clear specific data entry cells, however, it will
not remove the check marks from the various check boxes I have put into the
sheet for users to select. Can anyone help me with this? I would like the
clear button to clear out the cells and check boxes at the same time, giving
the user a new, clean page to begin data entry in for the next batch.

Ron,

Thank you. I am having difficulting pasting it into the right place so that
it will work properly.

Any assistance?

"Ron de Bruin" wrote:

Hi Mel

You can use a macro like this for control toolbox controls

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

For forms checkboxes use this

ActiveSheet.CheckBoxes.Value = False

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



"Mel" wrote in message ...
I have a sheet built that I have added a "Clear" button. I was easily able to
record a macro that will clear specific data entry cells, however, it will
not remove the check marks from the various check boxes I have put into the
sheet for users to select. Can anyone help me with this? I would like the
clear button to clear out the cells and check boxes at the same time, giving
the user a new, clean page to begin data entry in for the next batch.

Hi Mel

Are you using checkboxes of the Control toolbox or from the
Forms toolbar

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



"Mel" wrote in message ...
Ron,

Thank you. I am having difficulting pasting it into the right place so that
it will work properly.

Any assistance?

"Ron de Bruin" wrote:

Hi Mel

You can use a macro like this for control toolbox controls

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

For forms checkboxes use this

ActiveSheet.CheckBoxes.Value = False

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



"Mel" wrote in message ...
I have a sheet built that I have added a "Clear" button. I was easily able to
record a macro that will clear specific data entry cells, however, it will
not remove the check marks from the various check boxes I have put into the
sheet for users to select. Can anyone help me with this? I would like the
clear button to clear out the cells and check boxes at the same time, giving
the user a new, clean page to begin data entry in for the next batch.

From the Control toolbox.

My problem is I just can not tell where to paste the script into the macro
so that it will work.
I have tried placing it in various spots with no success.

"Ron de Bruin" wrote:

Hi Mel

Are you using checkboxes of the Control toolbox or from the
Forms toolbar

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



"Mel" wrote in message ...
Ron,

Thank you. I am having difficulting pasting it into the right place so that
it will work properly.

Any assistance?

"Ron de Bruin" wrote:

Hi Mel

You can use a macro like this for control toolbox controls

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

For forms checkboxes use this

ActiveSheet.CheckBoxes.Value = False

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



"Mel" wrote in message ...
I have a sheet built that I have added a "Clear" button. I was easily able to
record a macro that will clear specific data entry cells, however, it will
not remove the check marks from the various check boxes I have put into the
sheet for users to select. Can anyone help me with this? I would like the
clear button to clear out the cells and check boxes at the same time, giving
the user a new, clean page to begin data entry in for the next batch.

Hi Mel

Alt-F11
InsertModule from the menubar
paste the sub in there (see below)
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "test" and press Run

Sub test()
Dim obj
'clear some cells on the activesheet
Range("A1:A3,C1:C3,D10").ClearContents

'change checkbox values to Flase
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Object.Value = False
End If
Next
End Sub


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



"Mel" wrote in message ...
From the Control toolbox.

My problem is I just can not tell where to paste the script into the macro
so that it will work.
I have tried placing it in various spots with no success.

"Ron de Bruin" wrote:

Hi Mel

Are you using checkboxes of the Control toolbox or from the
Forms toolbar

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



"Mel" wrote in message ...
Ron,

Thank you. I am having difficulting pasting it into the right place so that
it will work properly.

Any assistance?

"Ron de Bruin" wrote:

Hi Mel

You can use a macro like this for control toolbox controls

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

For forms checkboxes use this

ActiveSheet.CheckBoxes.Value = False

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



"Mel" wrote in message ...
I have a sheet built that I have added a "Clear" button. I was easily able to
record a macro that will clear specific data entry cells, however, it will
not remove the check marks from the various check boxes I have put into the
sheet for users to select. Can anyone help me with this? I would like the
clear button to clear out the cells and check boxes at the same time, giving
the user a new, clean page to begin data entry in for the next batch.