Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
Forms or controltoolbox checkboxes ?
Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30
"Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30 "Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
Thanks for your help Ron
"Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30 "Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30 "Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
Test1 or test2
-- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30 "Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30 "Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to loop through multiple checkboxes
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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... ROn they are controltoolbox checkboxes, CheckBox1....CheckBox30 "Ron de Bruin" wrote: Forms or controltoolbox checkboxes ? Are the names something like CheckBox1....CheckBox30 -- Regards Ron de Bruin http://www.rondebruin.nl "ram" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checkboxes and code | Excel Programming | |||
Adding checkboxes via code to a workheet | Excel Programming | |||
Making All Checkboxes Run the Same Code | Excel Programming | |||
Loop for checkboxes | Excel Programming | |||
For each...loop for checkboxes | Excel Programming |