macro reference to checkboxes...HELP!!!

[Jedi Master]

OK, I have 20 checkboxes in column D, rows 5 to 24. They are
creatively numbered checkbox1 thru checkbox 20...these were added with
the control toolbox.

That said, I want to check the value of the checkbox and assign a 1 if
checked and 0 in not checked inside the corresponding cells.

I tried a macro:

Sub SheetScore()
Dim CBCounter As Integer
Dim RCounter as integer

RCounter = 5

For CBCounter = 1 To 20
If Worksheets("4S").Checkboxes(cbcounter).value = True Then
Worksheets("4S").Cells(RCounter, 4).value = 1
ElseIf Worksheets("4S").CheckBoxes(CBCounter).value = False
Then
Worksheets("4S").Cells(RCounter, 4).value = 0
End If
RCounter = RCounter + 1
Next CBCounter

did not work, then I relized that the following worked:

if worksheets("4S").checkbox1.value = true then
....

So I tried to modify my code in the macro to:

Sub SheetScore()
Dim CBCounter As Integer
Dim RCounter As Integer
Dim cbname As String

RCounter = 5

For CBCounter = 1 To 20
cbname = "CheckBox" & CBCounter
If Worksheets("4S").cbname.value = True Then
Worksheets("4S").Cells(RCounter, 4).value = 1
ElseIf Worksheets("4S").cbname.value = True Then
Worksheets("4S").Cells(RCounter, 4).value = 0
End If
RCounter = RCounter + 1
Next CBCounter

did not work!

Please help me!!!

 

[Bob Phillips]

Sub SheetScore()
Dim CBCounter As Integer
Dim RCounter As Integer
Dim cbname As String

RCounter = 5

For CBCounter = 1 To 20
cbname = "CheckBox" & CBCounter
Worksheets("4S").Cells(RCounter, 4).Value = -
Worksheets("4S").OLEObjects(cbname).Object.Value
RCounter = RCounter + 1
Next CBCounter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Watch the wrap-around. This

Worksheets("4S").Cells(RCounter, 4).Value = -
Worksheets("4S").OLEObjects(cbname).Object.Value

should be one line.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jedi Master]

Thanks a lot! I really appreciate the help!

Patrick