Hi Ramich. Here is one of at least several ways you might accomplish this task. Download the example for a better look.
ramich 1131115.zip
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Sub</font> Example()
<font color="#0000A0">Dim</font> MyCaptions() <font color="#0000A0">As</font> <font color="#0000A0">String</font>
<font color="#008000"> 'takes a look at each CheckBox in some worksheet. "Sheet1" is a reference to the</font>
<font color="#008000"> 'worksheet that contains your checkboxes. MyCaptions() is an uninitialized</font>
<font color="#008000"> 'string array variable that will be filled if any checkboxes are located and are checked</font>
<font color="#0000A0">If</font> ReturnCheckedCaptions(Sheets("Sheet1"), MyCaptions()) <font color="#0000A0">Then</font>
<font color="#008000"> 'eligible items were loaded into your string array</font>
<font color="#0000A0">Dim</font> x, s
s = "Items is your array..." & vbCrLf & vbCrLf
<font color="#0000A0">For</font> x = 0 <font color="#0000A0">To</font> UBound(MyCaptions)
s = s & MyCaptions(x) & vbCrLf
<font color="#0000A0">Next</font> x
MsgBox s
<font color="#0000A0">Else</font>
<font color="#008000"> 'not eligible items were located (None were checked). You array is empty.</font>
MsgBox "No items were located. Your array is empty..."
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Function</font> ReturnCheckedCaptions(sh <font color="#0000A0">As</font> Worksheet, <font color="#0000A0">ByRef</font> MyCaptions() <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
<font color="#0000A0">Dim</font> c <font color="#0000A0">As</font> OLEObject, x <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>
<font color="#008000"> 'this variable will not change unless one or more eligible items have been located</font>
x = -1
<font color="#0000A0">For</font> <font color="#0000A0">Each</font> c <font color="#0000A0">In</font> Me.OLEObjects
<font color="#0000A0">If</font> c.ProgId = "Forms.CheckBox.1" <font color="#0000A0">Then</font>
<font color="#008000"> 'Value refers to (Checked=True or UnChecked=False)</font>
<font color="#0000A0">If</font> c.Object.Value <font color="#0000A0">Then</font>
x = x + 1
<font color="#0000A0">ReDim</font> <font color="#0000A0">Preserve</font> MyCaptions(x)
MyCaptions(x) = c.Object.Caption
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">End</font> <font color="#0000A0">If</font>
<font color="#0000A0">Next</font>
<font color="#008000"> 'returns true if one or more items have been loaded into the array</font>
ReturnCheckedCaptions = (x <> -1)
<font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("10120069535331").value=document.all("10120069535331").value.replace(/<br \/>\s\s/g,"");document.all("10120069535331").value=document.all("10120069535331").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("10120069535331").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="10120069535331" wrap="virtual">
Sub Example()
Dim MyCaptions() As String
'takes a look at each CheckBox in some worksheet. "Sheet1" is a reference to the
'worksheet that contains your checkboxes. MyCaptions() is an uninitialized
'string array variable that will be filled if any checkboxes are located and are checked
If ReturnCheckedCaptions(Sheets("Sheet1"), MyCaptions()) Then
'eligible items were loaded into your string array
Dim x, s
s = "Items is your array..." & vbCrLf & vbCrLf
For x = 0 To UBound(MyCaptions)
s = s & MyCaptions(x) & vbCrLf
Next x
MsgBox s
Else
'not eligible items were located (None were checked). You array is empty.
MsgBox "No items were located. Your array is empty..."
End If
End Sub
Function ReturnCheckedCaptions(sh As Worksheet, ByRef MyCaptions() As String) As Boolean
Dim c As OLEObject, x As Integer
'this variable will not change unless one or more eligible items have been located
x = -1
For Each c In Me.OLEObjects
If c.ProgId = "Forms.CheckBox.1" Then
'Value refers to (Checked=True or UnChecked=False)
If c.Object.Value Then
x = x + 1
ReDim Preserve MyCaptions(x)
MyCaptions(x) = c.Object.Caption
End If
End If
Next
'returns true if one or more items have been loaded into the array
ReturnCheckedCaptions = (x <> -1)
End Function</textarea>
ramich 1131115.zip