CODEWITHSUNDEEP
excelvbaloopscheckbox
NewBoard
NewBoard

Reputation: 304

Trouble with VBA checkboxes

I am trying to create a macro that will print the selected sheets out of a workbook. The first worksheet has a set of Checkboxes that all follow the naming convention "CheckBoxX" where X is the number of that checkbox. What I'd like the code to do, is run through each of these checkboxes, see if it's checked, then select that corresponding worksheet if it is checked. However, I'm getting an error "Object doesn't support this property or method".

Here's my code: Code:

Option Explicit


Sub Button14_Click()
    Dim count As Integer
    Dim checkNumber As String

    For count = 1 To ThisWorkbook.Worksheets.count
        checkNumber = "CheckBox" & count
        If Sheets("Print").Shapes(checkNumber).OLEFormat.Object.Value = True Then
            Worksheets(count + 1).Select (False)
        End If
    Next count

    ActiveWindow.SelectedSheets.PrintOut

End Sub

The debug always flags the line with the if statement.

I have also tried this as my if statement:

If Sheets("Print").Shapes(checkNumber).ControlFormat.Value = 1 Then

SOLUTION: All I had to do was change this line:

If Sheets("Print").Shapes(checkNumber).OLEFormat.Object.Value = True Then

to this:

If Sheets("Print").Shapes(checkNumber).OLEFormat.Object.Object.Value = True Then

Upvotes: 0

Views: 1161

Answers (2)

PeterT
PeterT

Reputation: 8557

Assuming you're using the ActiveX type of checkbox (which the OLEObject in your code implies), you are accessing the list of Shape objects instead of the list of OLEObjects.

A quick test sub is:

Sub test()
    Dim mycb As Variant
    Set mycb = ActiveSheet.OLEObjects("CheckBox1")
    Debug.Print mycb.Name
    Debug.Print mycb.Object.Value
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

You might need "Checkbox 1" rather than "Checkbox1"

Upvotes: 0

Related Questions