asked on

check whether all of the checkboxes on active worksheet have the controlformat.value = 1 using VBA

Dear Experts:

On the active worksheet I got 519 check box controls (normal form checkboxes, not Active X control checkboxes).

They are named as follows: m_cb0001, m_cb0002, m_cb0003 to m_cb0519

I would like to run a macro with the following requirements:

If All of these checkboxes have the ControlFormat.Value = 0 the macro has to say so and exit the sub. If not, another macro called 'MyMacro_2' is to be called up.

The checkboxes in question (m_cb001,m_cb002 etc.) may also be hidden, i.e. the rows in which they reside may be hidden. So the macro has to look for the controlformat.Value in all of them, no matter if the checkboxes are hidden or not.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Roy Cox

Maybe

Sub LoopCheckBoxes()
Dim cb

    For Each cb In ActiveSheet.CheckBoxes
        If Not cb.Value = 1 Then MsgBox "Not all checkboxes are checked"
    Next
End Sub

Open in new window

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Dear both,

thank you very much for professional help. Both codes work just fine although I have to admit Roy's is more concise.

How do I rate the answers now? Both work fine, Roy was quicker and his code is more concise. Shall I now just acknowledge Roys' solution although Neeraj's code works fine as well?

I am not quite sure how to proceed ...

Subodh Tiwari (Neeraj)

In that case you can accept Roy's answer as Solution and mine as Assisted Solution.

Andreas Hermle

ASKER

Neeraj, ok, thank you for clearing this up. But, what is an assisted solution in EE talk?

Is it: "No, but it is helpful."?

Andreas Hermle

ASKER

Thank you very much to both of you. I really appreciate your professionalism. Regards, ANdreas

Subodh Tiwari (Neeraj)

You're welcome Andreas! Glad we could help.
Thanks for the feedback.

Roy Cox

Pleased to help