Solved: Cycle through Collection of Check boxes

[asingh]

Hi,

I have around 130 check boxes (the type which have back end VBA code), on an Excel tab. What code can I use, to cycle through the whole collection, and evaluate which ones are checked.

Thanks a lot for the help...

asingh

[mikerickson]

This will poll both the ActiveX and Forms menu checkboxes of the active sheet.
Dim oneControl As Object
For Each oneControl in ActiveSheet.Shapes
With oneControl
If .Name Like "Check*" Then
MsgBox .Name & ".has the value " & .ControlFormat.Value
End If
End With
Next oneControl

[asingh]

Hi,

I am getting the error "object doesnt support this property or method" at the line:

MsgBox .Name & ".has the value " & .ControlFormat.Value

I also tried the following:

Dim oneControl As Object

Dim cntr As Integer

Sheets("Sheet1").Activate

For Each oneControl In ActiveSheet.Shapes
With oneControl
If .Name Like "Check*" Then

If .ControlFormat.Value = True Then
cntr = cntr + 1
End If

End If
End With
Next oneControl

Getting the same error..!

[lucas]

That's a lot of controls on one sheet.. Why not use a regular checkmark.....the letter a as a marlett font.

This example uses sheetselection to add the letter a to column a if clicked or remove it.

the macro deletes the rows with checkmarks.......

[asingh]

Yes..I know its a lot of controls..but thats an initial..user design. Got to work with that...

So your file..thanks..but I still need a solution..to my original...problem..!

[lucas]

Maybe this is what you mean:

Option Explicit
Sub checked()
Dim oCB As OLEObject
With Sheets("Sheet1")
For Each oCB In .OLEObjects
If TypeName(oCB.Object) = "CheckBox" Then
If oCB.Object.Value = True Then
MsgBox oCB.Name & " is checked"
End If
End If
Next oCB
End With
End Sub

[mikerickson]

I can't test ActiveX control properties on my Mac here at home.
Try using the object Browser, perhaps .OLEObject.Object.Value will work.

Or looping through the ActiveSheet.OLEObjects or ActiveSheet.Controls collection.

[asingh]

Yes..just tried it..the solution now is working...! It has to be done using OLEObject..perfect.

Marking this as solved..! Thanks again.