CODEWITHSUNDEEP
vba
RoryG
RoryG

Reputation: 1123

Cycling through some checkboxes on my excel page

I am VERY new to VBA (and know of it only within excel).

I am trying to cycle through some (but not all) checkboxes. They are currently named CheckBox1 through CheckBox15. Howe do I cycle through say for instance CheckBox5 through CheckBox10?

I guess I am hoping there is a 'method' similar to 'CheckType' for controls that will allow me to check name?

Here is what I have tried. Causes a Compile Error - Sub or Function not defined, and highlights Worksheet.

Private Sub BoxCheck()
atLeastOneChecked = False
    For i = 2 To 4
        If Worksheets("ActiveX").Controls("Checkbox" & i).Value = True Then
            atLeastOneChecked = True
        End If
    Next i
End Sub

While the above doesnt work, what is below does:

Private Sub BoxCheck()
    atLeastOneChecked = False

    For i = 1 To 2
       If Sheet2.CheckBox2.Value = True Then
                atLeastOneChecked = True
       End If
    Next i

End Sub

Of course, the loop has no affect on the outcome, but it compiles and atLeastOneChecked turns from False to True when Checkbox2 is True. Note that Sheet2 has been named ActiveX. I clearly don't understand how Worksheet and Controls work. Could someone help?

After fixing the error described below, this still won't work. I simplified to the following:

Private Sub BoxCheck()
    Dim ole As OLEObject

    atLeastOneChecked = False
    Set ole = Sheets("ActiveX").OLEObjects("Checkbox2")
    If ole.Value = True Then
        atLeastOneChecked = True
    End If
 End Sub

This doesn't work. It fails at:

If ole.Value = True Then

The error states: Object Doesn't support this property or method

This is true for OLEObjects, but not for Checkboxes. When I look at the properties of ole, I see that its Object property is set to Object/Checkbox and that this Object has a value. I guess that is what I should be referencing in my if statement, but I don't know how.

Upvotes: 0

Views: 9233

Answers (3)

RoryG
RoryG

Reputation: 1123

I think I solved the problem.

The value of the Checkbox is accessed by Referencing the Object property within the OLEObject I set...Like this:

If ole.Object.Value = True Then

Thanks for all your help. If someone has a more elegant solution, I would still like to see it.

Upvotes: 1

Kevin Pope
Kevin Pope

Reputation: 2982

To expand on @Parker's answer:

Private Sub BoxCheck()
    atleastonechecked = False
    Dim oles As OLEObject
    For i = 2 To 4
        'If you're using Shapes Controls:
        If ThisWorkbook.Worksheets("ActiveX").Shapes("Check Box " & i).Value = True Then
            atleastonechecked = True
        End If
        ' If you're using ActiveX Controls
        Set oles = ThisWorkbook.Worksheets("ActiveX").OLEObjects("CheckBox" & i)
        If oles.Value = True Then
            atleastonechecked = True
        End If
    Next i
End Sub

Sorry, just put together the previous answer without testing - that always fails. Just use the If loop based on what type of control you're using.

Upvotes: 0

Parker
Parker

Reputation: 1102

Use CheckBox.Name

Example:

For Each cb In ActiveSheet.CheckBoxes 
    If cb.Name = "CheckBox5"
        ' Do stuff
    End If
Next cb

Upvotes: 0

Related Questions