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:

 

[Domenic]

Your code is looping through CheckBoxes named "ck_1" through "ck_10". And so you'll get that error if your worksheet does not contain a CheckBox with one of those names.

 

[sharshra]

Your code is looping through CheckBoxes named "ck_1" through "ck_10". And so you'll get that error if your worksheet does not contain a CheckBox with one of those names.

CheckBoxes (ck_1 through ck_10) are there. Now I´m setting their value to false one by one & it works fine. I want to make it better by using for loop.

 

[Domenic]

Your code refers to the active workbook, since you haven't qualified your sheet reference. Is the workbook in which the CheckBoxes are located in fact the active workbook when running the code?

 

[sharshra]

Your code refers to the active workbook, since you haven't qualified your sheet reference. Is the workbook in which the CheckBoxes are located in fact the active workbook when running the code?

Yes. Checkboxes are in the active workbook.

As mentioned before, when I use the following code separately for each of 10 checkboxes, it is working correctly. But when I use the for loop, it is throwing error.

Worksheets("Sheet1").Ck_1.Value = False

 

[Domenic]

It looks like your CheckBoxes are from ActiveX controls, not Form controls. Try the following instead...

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

 

[sharshra]

It looks like your CheckBoxes are from ActiveX controls, not Form controls. Try the following instead...

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

Checkboxes are form controls only. Not ActiveX controls.

 

[Domenic]

Let's check whether or not your CheckBoxes are in fact Form controls. First, make sure that the workbook containing your CheckBoxes is the active workbook. Then enter the following line in the Immediate Window (Visual Basic Editor >> View >> Immediate Window), and press ENTER...

? typeof Worksheets("Sheet1").Ck_1 is Excel.CheckBox

What does it return, True or False?

 

[sharshra]

Let's check whether or not your CheckBoxes are in fact Form controls. First, make sure that the workbook containing your CheckBoxes is the active workbook. Then enter the following line in the Immediate Window (Visual Basic Editor >> View >> Immediate Window), and press ENTER...

? typeof Worksheets("Sheet1").Ck_1 is Excel.CheckBox

What does it return, True or False?

The code above is returning False.

I tried using the code suggested for ActiveX controls, but getting the run time error 438 (object doesn´t support this property or method)

 

[Domenic]

The code above is returning False.

Okay, so that confirms that it's not a Form control

I tried using the code suggested for ActiveX controls, but getting the run time error 438 (object doesn´t support this property or method)

In that case, try entering the following line in the Immediate Window...

? typename(Worksheets("Sheet1").Ck_1)

What does it return?