Code to loop through multiple checkboxes

[Guest]

I have 30 check boxes on a spreadsheet I want to copy the values over to a
table to keep the history of the check box values each time the survey is
completed.

How can I Create a loop that retrives the value from each check box then
copies the value to the first blank row in my table. then changes the values
to all the checkboxes to false.

Thanks for any help

 

[Ron de Bruin]

Forms or controltoolbox checkboxes ?

Are the names something like CheckBox1....CheckBox30

 

[Guest]

ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30

 

[Ron de Bruin]

Hi Ram

I think I will use a linkedcell (see properties of each checkbox)
Use cells in a row below your data.
You can hide that row

Then you can use code from this page to copy for example A50:AD50 to the database sheet
http://www.rondebruin.nl/copy1.htm

This two examples change the values to false

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

Or this one

Sub Test2()
For i = 1 To 30
ActiveSheet.OLEObjects("CheckBox" & i). _
Object.Value = False
Next
End Sub

 

[Guest]

Thanks for your help Ron

Hi Ram

I think I will use a linkedcell (see properties of each checkbox)
Use cells in a row below your data.
You can hide that row

Then you can use code from this page to copy for example A50:AD50 to the database sheet
http://www.rondebruin.nl/copy1.htm

This two examples change the values to false

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

Or this one

Sub Test2()
For i = 1 To 30
ActiveSheet.OLEObjects("CheckBox" & i). _
Object.Value = False
Next
End Sub

 

[Guest]

When I try to change the check box value i recieve the the following error
message
unable to get the oleobjects property of the worksheet class

Thanks for any help

 

[Ron de Bruin]

Test1 or test2

 

[Guest]

I deleted the first checkbox so it couldn't find check box 1. I changed the
code, now it runs fine.

Thanks again

 

[Ron de Bruin]

Note that Test1 loop through all checkboxes
So if the 30 checkboxes are the only checkboxes on the sheet use this example

No problem when you rename the checkboxes