Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 05, 03:40 PM
Mel
 
Posts: n/a
Default 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.

  #2   Report Post  
Old May 20th 05, 03:53 PM
Ron de Bruin
 
Posts: n/a
Default

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.



  #3   Report Post  
Old May 20th 05, 04:22 PM
Mel
 
Posts: n/a
Default

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.




  #4   Report Post  
Old May 20th 05, 04:27 PM
Ron de Bruin
 
Posts: n/a
Default

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.






  #5   Report Post  
Old May 20th 05, 07:17 PM
Mel
 
Posts: n/a
Default

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.








  #6   Report Post  
Old May 20th 05, 08:35 PM
Ron de Bruin
 
Posts: n/a
Default

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.










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to clear cells Esrei Excel Discussion (Misc queries) 5 April 19th 05 01:52 PM
How do I link a check box to a macro? Tom Excel Worksheet Functions 1 April 12th 05 04:37 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 10:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 12:40 AM
Macro Formula revision? Mark Excel Worksheet Functions 1 November 28th 04 01:43 AM


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017