Simplify macro with a loop

[xenedra13]

I have a macro with 1 sub that calls 52 individual subs if a certain checkbox is clicked. I am wanting to create a loop that will get rid of having to have 52 similar but not identical subs (one for each row of data). I need the loop to check and see if each one of the checkboxes are checked and if they are, then do a bunch of stuff on that line. I'm not sure how to go about including the checkboxes in the loop. Can someone give me an outline of how that would work? The checkboxes are named "Check Box 2" through "Check Box 53".

 

[MickG]

Perhaps something like this:-

[COLOR="Navy"]Sub[/COLOR] MG20Aug47
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sh [COLOR="Navy"]As[/COLOR] OLEObject
 
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] sh [COLOR="Navy"]In[/COLOR] ActiveSheet.OLEObjects
    [COLOR="Navy"]If[/COLOR] TypeName(sh.Object) = "CheckBox" [COLOR="Navy"]Then[/COLOR]
        k = k + Abs(CLng(sh.Object.Value))
        c = c + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] sh
[COLOR="Navy"]If[/COLOR] c = k [COLOR="Navy"]Then[/COLOR]
    MsgBox "All Checked " '[COLOR="Green"][B] Do something Here !!![/B][/COLOR]
[COLOR="Navy"]Else[/COLOR]
    MsgBox k & " Out Of " & c & " Checked"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Snakehips]

xenedra13,


Maybe....

Sub Check()
    For Each CheckBox In ActiveSheet.CheckBoxes
        If CheckBox.Index > 1 And CheckBox.Index < 54 And CheckBox.Value = 1 Then
            'Do whatever and relate the row number to the checkbox index
            MsgBox "Do your stuff where row is  " & CheckBox.Index
            Else
            'do nothing?
        End If
    Next CheckBox
End Sub

Hope that helps.

 

[NeonRedSharpie]

For checkboxes, you can add a "Linked Cell" that will be true/false based on the checkbox value. Then you can loop through those cells and look for true (checked) or false (unchecked).

 

[xenedra13]

Mick, Could you explain exactly what your code does? I would like to understand it better instead of just copying it. I'm trying to learn as I go. Thanks!

 

[xenedra13]

For checkboxes, you can add a "Linked Cell" that will be true/false based on the checkbox value. Then you can loop through those cells and look for true (checked) or false (unchecked).

NeonRedSharpie, I have tried to use the linked cells and refer to them in the macro, but I have never been able to get it to work. Each time I run the macro it just doesn't do anything. There is no change. Do you know of a way around this so that I can use that? I used the following code:

If Range("A1").Value = "TRUE" Then...

Nothing worked. Any help is much appreciated! Thank you!

 

[NeonRedSharpie]

It returns a boolean. So while the cell shows "TRUE", it is actually just TRUE. You don't need the quotes. You actually don't even need the = TRUE part.

If Range("A1").Value = TRUE Then...

Fun quirk about excel. What the above code means is:

IF

[Range("A1").Value = TRUE ] = TRUE

Then...

All you need is:

If Range("A1").Value Then...

This is saying:

IF

[Range("A1").Value] = TRUE

Then...


Now it's not bad syntax to include the additional = TRUE. Just now that a boolean response where the cell is equal to TRUE (or =TRUE()) is NOT the same as the string "TRUE".

 

[xenedra13]

It returns a boolean. So while the cell shows "TRUE", it is actually just TRUE. You don't need the quotes. You actually don't even need the = TRUE part.

If Range("A1").Value = TRUE Then...

Fun quirk about excel. What the above code means is:

IF

[Range("A1").Value = TRUE ] = TRUE

Then...

All you need is:

If Range("A1").Value Then...

This is saying:

IF

[Range("A1").Value] = TRUE

Then...


Now it's not bad syntax to include the additional = TRUE. Just now that a boolean response where the cell is equal to TRUE (or =TRUE()) is NOT the same as the string "TRUE".

I didn't know that. That helps a lot! Thanks!