Copying Checkbox Values into a different worksheet

  • Thread starter Thread starter Thiery Balser
  • Start date Start date
T

Thiery Balser

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.
 
Back
Top