Looping through checkboxes in a specified range

[MotroJena]

Hi,

I have a sheet with checkboxes in columns A e B in many lines and I want to perform some actions based on wheter or not the checkboxes are checked. But I have to do it in each line at a time in a loop. But I am failing in trying to do it, since the OLEobjects apparently are only available to the worksheet object. And also I need help to find the right sintax (the typeof apparently is only valid to useform, not to activex controls.

Sorry my bad english

[MotroJena]

The relevant sheet is sheet1

[protonLeah]

I want to perform some actions based on ...the checkboxes...

You nee to describe the 'actions'. I can't tell from the macro code or the worksheets

[MotroJena]

You nee to describe the 'actions'. I can't tell from the macro code or the worksheets

Ok, I just need to access those checkboxes, for instance, its names, inside the loop; each line at time, has mentioned

[jindon]

Example

Please Login or Register  to view this content.

[MotroJena]

Example

Please Login or Register  to view this content.

Hi thanks for the help

I debug the code I think this part is giving a problem since the statement fails:
If TypeName(ob.Object) = "CheckBox" Then

I replace the checkbox name to the actual checkboxes names ("checkbox1", "checkbox2" ) and still doesn't work.

[Artik]

How about a completely different solution that will allow you to operate on cell values, rather than looking for which checkbox is checked.
With the following macro, which you run once, you will insert checkboxes in columns E:F. Each checkbox is linked to the cell in which that checkbox is embedded. Selecting a checkbox will insert a TRUE value into the linked cell, unselecting it will insert a FALSE value. In order to make logical values not visible in the cells, a special format (;;;) is used. If the list gets longer in the future, you can run this macro again. Previous selections will be retained.
Such a solution will allow you to operate on cell values, whether with another macro or with formulas in the worksheet.

Caution, the macro removes all graphic objects from the sheet. If, in addition to the checkboxes in columns E:F, there will be other objects, these controls should be removed differently.

Please Login or Register  to view this content.

Artik

[jindon]

You could check it for yourself.

Please Login or Register  to view this content.

Use progID or TypeName whichever useful.

[MotroJena]

How about a completely different solution that will allow you to operate on cell values, rather than looking for which checkbox is checked.
With the following macro, which you run once, you will insert checkboxes in columns E:F. Each checkbox is linked to the cell in which that checkbox is embedded. Selecting a checkbox will insert a TRUE value into the linked cell, unselecting it will insert a FALSE value. In order to make logical values not visible in the cells, a special format (;;;) is used. If the list gets longer in the future, you can run this macro again. Previous selections will be retained.
Such a solution will allow you to operate on cell values, whether with another macro or with formulas in the worksheet.

Caution, the macro removes all graphic objects from the sheet. If, in addition to the checkboxes in columns E:F, there will be other objects, these controls should be removed differently.

Please Login or Register  to view this content.

Artik

Man, It worked beautifully. In fact it is much easier to work with cells values.
I can't say thank you enough

Just out of curiousity: what those lines do:

Application.ScreenUpdating = False

On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0

what runtime error it could fire that could be ignored?

[Artik]

Please Login or Register  to view this content.

Freezes the screen for the duration of the procedure.

Please Login or Register  to view this content.

If it were not for error handling this line would report an exception if there were no graphic objects in the sheet.

Artik

[MotroJena]

How about a completely different solution that will allow you to operate on cell values, rather than looking for which checkbox is checked.
With the following macro, which you run once, you will insert checkboxes in columns E:F. Each checkbox is linked to the cell in which that checkbox is embedded. Selecting a checkbox will insert a TRUE value into the linked cell, unselecting it will insert a FALSE value. In order to make logical values not visible in the cells, a special format (;;;) is used. If the list gets longer in the future, you can run this macro again. Previous selections will be retained.
Such a solution will allow you to operate on cell values, whether with another macro or with formulas in the worksheet.

Caution, the macro removes all graphic objects from the sheet. If, in addition to the checkboxes in columns E:F, there will be other objects, these controls should be removed differently.

Please Login or Register  to view this content.

Artik

Hi Artik,

In the above worksheet, urged the need to create a command button, which opens an userform, but this code, as you said before, is deleting the button. is there a way to keep the command button?
Thanks!

[srimuir]

Thanks! Artik

[srimuir]

I replace the checkbox name to the actual checkboxes names ("checkbox1", "checkbox2" ) and still doesn't work.

[Artik]

Hi Artik,

In the above worksheet, urged the need to create a command button, which opens an userform, but this code, as you said before, is deleting the button. is there a way to keep the command button?

Instead of lines:

Please Login or Register  to view this content.

insert:

Please Login or Register  to view this content.

and add procedure in the module:

Please Login or Register  to view this content.

Artik

[PrizeGotti]

Click on your CheckBox to select it, in the Developer tab (make sure you have that enable), select the "Properties" function. In the box that says "Cell Link", reference the cell the Checkbox is located.

You can then use that cell as reference to determine if the box is checked, just as you would when looping through cells.

You can also you my tutorial on how to add Boolean Checkboxes prior to their official implementation later this year: https://www.excelforum.com/tips-and-...tml?highlight=

[MotroJena]

Instead of lines:

Please Login or Register  to view this content.

insert:

Please Login or Register  to view this content.

and add procedure in the module:

Please Login or Register  to view this content.

Artik

Thank you once again!