Check state of a number of checkboxes

[hstaubyn]

Hi,

I have a spreadsheet in which I run a macro to insert checkboxes in some cells depending on the value of the next door cell. The name of each checkbox contains the row number. This works fine.

What I am trying to do now is write another one where each one is investigated to see if it is checked and, if so, store the number part of the checkbox name in an array. To do this I first ReDim the array to have the same number of elements as the number of 'TRUE' (checked) checkboxes on the worksheet. This is the code I am trying to use to count the number of TRUE check boxes.

Function Count_Positive_Checkboxes()
 
Dim Btn As Object
Dim PosCount As Long
 
PosCount = 0
 
For Each Btn In ActiveSheet.Shapes
    If Btn.Value = True Then
        PosCount = PosCount + 1
    End If
Next Btn
 
Count_Positive_Checkboxes = PosCount
 
End Function

The only objects on the worksheet are the checkboxes.

I get a run-time error 438 though with a message saying 'Object doesn't support this property or method'

Can anyone tell me what I'm doing wrong?

 

[Gary McMaster]

Since you are iterating the shapes collection, I'm assuming you are sure that these are form controls.

If my assumption is correct, please try this:

Function Count_Positive_Checkboxes()
 
Dim Btn As Shape
Dim PosCount As Long
 
PosCount = 0
 
For Each Btn In ActiveSheet.Shapes
    If Btn.ControlFormat.Value = Checked Then
        PosCount = PosCount + 1
    End If
Next Btn
 
Count_Positive_Checkboxes = PosCount
 
End Function

Gary

 

[Gary McMaster]

You could also try it this way:

Public Sub Test()

Dim oCheckBox As CheckBox

For Each oCheckBox In ActiveSheet.CheckBoxes
    If oCheckBox.Value = 1 Then
        Debug.Print oCheckBox.Name & vbTab & "Checked"
    Else
        Debug.Print oCheckBox.Name & vbTab & "Un-Checked"
    End If
Next oCheckBox

End Sub

Gary

 

[hstaubyn]

Thanks, that works perfectly