Looping thru Checkboxes in a worksheet

[sharshra]

I have to loop thru check boxes in a worksheet & was searching for a solution. I found the following post from mrexcel excel questions forum. Post #2 is exactly what I need.

    Dim i As Long
    
    For i = 1 To 10
        Worksheets("Sheet1").CheckBoxes("ck_" & i).Value = False
    Next i

But, when I use this, I´m getting an error shown below. Not sure what is going wrong. Can the experts in this forum help please?

Link to a post from mrexcel excel questions forum:
[VBA] loop through all check box buttons on a sheet

Error:

 

[sharshra]

What does it return?

It is returning CheckBox

 

[Domenic]

So far we've determined that it's not a Form control, but it is a CheckBox. So it must be an ActiveX control. And we should be able to confirm this with the following...

? typeof worksheets("Sheet1").ck_1 is MSForms.CheckBox

It should return True. Does it?

Also, try fully qualifying your reference. For example, if the workbook running the code contains the CheckBoxes...

ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_" & i).Object.Value = False

Otherwise...

 Workbooks("Book2.xlsx").Worksheets("Sheet1").OLEObjects("ck_" & i).Object.Value = False

 

[sharshra]

Thanks Domenic for your advice so far. Yes, the following returns True.

? typeof worksheets("Sheet1").ck_1 is MSForms.CheckBox

But, I have problem with next set of codes to assign a value to CheckBox. When the code is executed, I´m getting the error shown below in the following line.

thisworkbook.Worksheets ("Sheet1").oleobjects("ck_" & i).object.value = False

In the immediate window, I get the correct results for checkbox. For example, following code in immediate window show me the current value (true or false) of checkbox.

?thisworkbook.Worksheets ("Sheet1").oleobjects("ck_1").object.value

 

[Domenic]

Hmmm... this is very perplexing. If that line of code succeeds in the Immediate Window, then your other line should succeed as well. The only way I get that error is if I omit .Object from the reference. Maybe it might help if you posted your complete code.

 

[sharshra]

Yes, it is puzzling
My code is simple. I´m just using a For loop to assign False to checkbox Value.

For i = 1 To 10
    ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_" & i).Object.Value = False
Next i

The code returns correct value in immediate window but failing in runtime

?ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_" & i).Object.Value

 

[Jaafar Tribak]

Do you have any oleobjects in the worksheet other than the checkboxes and which have a similar ck_i name pattern?

EDIT:
Try running this to find out what is in the worksheet:

    Dim oObj As Object
    For Each oObj In ThisWorkbook.Worksheets("Sheet1").OLEObjects
        Debug.Print oObj.Name, TypeName(oObj.Object)
    Next oObj

 

[sharshra]

No. I have only checkboxes in the worksheet. There are no other controls.

When I run the code mentioned above, I see only ck_1 to 10.

 

[Jaafar Tribak]

Yes, it is puzzling
My code is simple. I´m just using a For loop to assign False to checkbox Value.

For i = 1 To 10
    ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_" & i).Object.Value = False
Next i

The code returns correct value in immediate window but failing in runtime

?ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_" & i).Object.Value

Does the code fail at the first iteration? ie:= when i =1 or at subsequent calls ? Also, do you have any sheet protection in place?

 

[sharshra]

It is failing in the first instance itself. Sheet is not protected.

I also tried using checkbox name outside the For loop, but still getting the same error.

ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_1").Object.Value = False

 

[Jaafar Tribak]

Try running this code and see on which line the runtime error occurs: ( and tell us exactly which error if any)

Dim v As Variant

Set v = ThisWorkbook.Worksheets("Sheet1")
Set v = ThisWorkbook.Worksheets("Sheet1").OLEObjects
Set v = ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_1")
Set v = ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_1").Object
v = ThisWorkbook.Worksheets("Sheet1").OLEObjects("ck_1").Object.Value