The aim of my code is:
1) loop thru all CheckBoxes in ActiveSheet;
2) if CheckBox is checked (i.e. its Value property is True), then we add its Index to array vArray.
Here's the code:

1 Dim i As Integer
2 Dim vArray() As Variant
3 Dim cb As CheckBox
4 i = 1
5 For Each cb In ActiveSheet.OLEObjects
6 ReDim vArray(1 To i)
7 vArray(i) = cb.Index
8 i = i + 1
9 Next

VBA shows this error: Type mismatch in line 5. What's wrong?

Check if the following works out:

Sub Chkbxes()



 Dim i As Integer
 Dim vArray() As Variant
 Dim cb As OLEObject
 i = 1
 For Each cb In ActiveSheet.OLEObjects
    If cb.progID = "Forms.CheckBox.1" Then
        ReDim vArray(1 To i)
        vArray(i) = cb.Index
        i = i + 1
    End If
 Next
End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Shasur, thanks a lot!!!
But there was one error which I mentioned and corrected myself: you should use Preserve keyword after ReDim. When I corrected it, things went right :) Thanks once again!

Make it
ReDim Preserve vArray(1 To i)
in order for the array items to be preserved through the consecutive ReDims

Oh Ok, I see you got it yourself! Good work sektor!
Regards

And what about adding some condition? This code generates error 'cause VBA can't recognize Index property of CheckBox (line 7):

1 Dim ole As OLEObject
2 Dim vArray() As Variant
3
4 For Each ole In ActiveSheet.OLEObjects
5 If TypeName(ole.Object) = "CheckBox" Then
6 ReDim Preserve vArray(1 To i)
7 vArray(i) = ole.Object.Index
8 i = i + 1
9 End If
10 Next

Dim ole As OLEObject
Dim i As Integer
Dim vArray() As Variant
i = 1
For Each ole In ActiveSheet.OLEObjects
   If TypeName(ole.Object) = "CheckBox" Then
      ReDim Preserve vArray(1 To i)
      vArray(i) = ole.Index
      i = i + 1
   End If
Next

I seem to slept few :) I forgot to write "i=1". Now all works fine! Thanks a lot!