Copying Checkbox Values into a different worksheet

Hi there,



I'm trying to copy the value of several checkboxes to another worksheet.

This works basically all fine by using the code below

Sub EvaluateCB()
ActiveWorkbook.Worksheets("Survey").Select
Range("A1").Select
d = Worksheets("Sheet1").CheckBox1.Value
Selection = d
End Sub


But, as there are about 40 checkboxes I wonder whether there is a way to do
this all automatically, i.e. by some for loop going through all the
checkboxes.

As I am a complete rookie in Excel I appreciate any hint / suggestions.


Many thanks,



Thiery

You may want to just change the linkedcell to point at a cell on that second
sheet.

But something like this should work, too:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim CBWks As Worksheet
Dim SurvWks As Worksheet
Dim oRow As Long

Set CBWks = Worksheets("Sheet1")
Set SurvWks = Worksheets("Survey")

oRow = 1
For Each OLEObj In CBWks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
With SurvWks.Cells(oRow, "A")
.Value = OLEObj.Object.Value
.Offset(0, 1).Value = OLEObj.TopLeftCell.Address(0, 0)
.Offset(0, 2).Value = OLEObj.Object.Caption
End With
oRow = oRow + 1
End If
Next OLEObj

End Sub

I brought over the address where the checkbox is located, too--and it's caption.

Thiery Balser wrote:
>
> Hi there,
>
> I'm trying to copy the value of several checkboxes to another worksheet.
>
> This works basically all fine by using the code below
>
> Sub EvaluateCB()
> ActiveWorkbook.Worksheets("Survey").Select
> Range("A1").Select
> d = Worksheets("Sheet1").CheckBox1.Value
> Selection = d
> End Sub
>
> But, as there are about 40 checkboxes I wonder whether there is a way to do
> this all automatically, i.e. by some for loop going through all the
> checkboxes.
>
> As I am a complete rookie in Excel I appreciate any hint / suggestions.
>
> Many thanks,
>
> Thiery

--

Dave Peterson