Looping Checkboxes in Worksheet

[Lin]

Hi
I have a set of checkboxes in worksheet. I am using the following code to
get the value of the checkbox if it's checked. But Some how it is not working
at all. I am not getting error either.

Dim k As Integer
Dim CH(1 To 5) As Object
Set CH(1) = CH1
Set CH(2) = CH2
Set CH(3) = CH3
Set CH(4) = CH4
Set CH(5) = CH5


For k = 1 To 5
With Worksheets("Sheet1")
If CH(k).Value = True Then
Worksheets("Sheet2").[A2] = CH(k).Caption
Else
Worksheets("Sheet2").[A2] = " "
End If
End With
Next k
Any help really appreciate
Thanks

 

[Peter T]

Checkboxes from the Forms or Control Toolbox menu

Is the code in a normal module or the sheet module with the checkboxes

Regards,
Peter T

 

[Lin]

Checkboxes are in the Control Toolbox.
Code is in Normal module

Checkboxes from the Forms or Control Toolbox menu

Is the code in a normal module or the sheet module with the checkboxes

Regards,
Peter T

Hi
I have a set of checkboxes in worksheet. I am using the following code to
get the value of the checkbox if it's checked. But Some how it is not
working
at all. I am not getting error either.

Dim k As Integer
Dim CH(1 To 5) As Object
Set CH(1) = CH1
Set CH(2) = CH2
Set CH(3) = CH3
Set CH(4) = CH4
Set CH(5) = CH5


For k = 1 To 5
With Worksheets("Sheet1")
If CH(k).Value = True Then
Worksheets("Sheet2").[A2] = CH(k).Caption
Else
Worksheets("Sheet2").[A2] = " "
End If
End With
Next k
Any help really appreciate
Thanks

 

[Peter T]

In a NEW workbook run test1 and test2

Sub test1()
Dim i As Long
Dim ole As OLEObject
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Activate
ws.OLEObjects.Delete ' << only on a test sheet !
For i = 1 To 5
Set ole = ws.OLEObjects.Add("Forms.Checkbox.1")
With ws.Cells(i * 3, 2)
ole.Left = .Left
ole.Top = .Top
End With
ole.Name = "CH" & i
ole.Object.Caption = "Check it " & i
ole.Object.Value = i Mod 2
Next
End Sub

Sub test2()
Dim i As Long
Dim r As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ole As OLEObject

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Set r = ws2.Range("A2").Resize(5, 2)

For i = 1 To 5
Set ole = ws1.OLEObjects("CH" & i)
r(i, 1) = ole.Object.Caption
r(i, 2) = IIf(ole.Object.Value, "On", "")
Next
ws2.Activate
End Sub

Regards,
Peter T

Checkboxes are in the Control Toolbox.
Code is in Normal module

Checkboxes from the Forms or Control Toolbox menu

Is the code in a normal module or the sheet module with the checkboxes

Regards,
Peter T

Hi
I have a set of checkboxes in worksheet. I am using the following code
to
get the value of the checkbox if it's checked. But Some how it is not
working
at all. I am not getting error either.

Dim k As Integer
Dim CH(1 To 5) As Object
Set CH(1) = CH1
Set CH(2) = CH2
Set CH(3) = CH3
Set CH(4) = CH4
Set CH(5) = CH5


For k = 1 To 5
With Worksheets("Sheet1")
If CH(k).Value = True Then
Worksheets("Sheet2").[A2] = CH(k).Caption
Else
Worksheets("Sheet2").[A2] = " "
End If
End With
Next k
Any help really appreciate
Thanks