Using loop to run certain macros in Excel based on checked check boxes

Question

I have a spreadsheet template that needs to run different macros based on which check boxes are ticked before a button is pressed. I wrote this in an if statement which was fine with a couple of check boxes but gets exponentially longer as to the number of ifs you need to build in to work out all the scenarios. Right now there are 6 boxes that could be checked and this should run for all the possibilities.

Let's keep it simple and call the check boxes CB1 - CB6 and they should run corresponding macros M1 - M6 depending on which CB's are checked, in any combination (ie, you could choose to run CB1 and CB5 to run M1 and M5, or you could choose all 6 to run all 6 macros).

Right now it looks like:

Sub Checkboxes()
If ActiveSheet.CB("CB1").Value = 1_
and ActiveSheet.CB("CB2").Value = 1 Then
Call M1
Call M2
ElseIf ActiveSheet.CheckBoxes"CB1").Value = 1_
And ActiveSheet.CheckBoxes("CB2").Value = 0 Then
Call M1
ElseIf ActiveSheet.CheckBoxes("CB2").Value = 1_
And ActiveSheet.CheckBoxes("CB1").Value = 0 Then
Call M2
Else: MsgBox "Please select at least one option to proceed."
End If
End Sub

But you can see how writing an if statement for every scenario gets VERY long and surely not the best way to write it.

"ElseIf ActiveSheet.CheckBoxes("CB1").Value = 1_ And ActiveSheet.CheckBoxes("CB1").Value = 0 Then" can't happen — sous2817
– sous2817, Commented Nov 3, 2015 at 2:50
What do you mean? This is an example of how it currently works. — wmab
– wmab, Commented Nov 3, 2015 at 2:53
Checkboxes("CB1") can't be 1 and 0 at the same time. You have a typo. It should be Checkboxes("CB2") .Value = 1 and Checkboxes("CB1").Value = 0. Either way, I think there's a simpler approach (as I have outlined in my answer) — sous2817
– sous2817, Commented Nov 3, 2015 at 2:55

sous2817 · Accepted Answer · 2015-11-03 03:23:14Z

1

It seems you may be over complicating things (or I'm not entirely sure what you're after). If there are 6 checkboxes and if checkbox1 is ticked, run macro1. checkbox2 is ticked, run macro 2, etc then you can just do something like:

If ActiveSheet.CB("CB1").Value = 1 then Call M1
If ActiveSheet.CB("CB2").Value = 1 then Call M2
If ActiveSheet.CB("CB3").Value = 1 then Call M3
If ActiveSheet.CB("CB4").Value = 1 then Call M4
If ActiveSheet.CB("CB5").Value = 1 then Call M5
If ActiveSheet.CB("CB6").Value = 1 then Call M6

Right? There shouldn't be a reason to a long, drawn out "If, elseif" clause to cover the different scenarios.

To check if any code ran, here is one way. There are more elegant ways, but this should get you started and be easy enough for you to see what's going on and extend a bit if you need to. As you gain more experience with VBA, you'll undoubtedly refactor this to be more graceful.

Sub Test()
Dim AtLeastOneRan As Boolean

    If ActiveSheet.CB("CB1").Value = 1 Then
        AtLeastOneRan = True
        Call M1
    End If

    If ActiveSheet.CB("CB2").Value = 1 Then
        AtLeastOneRan = True
        Call M2
    End If

    If ActiveSheet.CB("CB3").Value = 1 Then
        AtLeastOneRan = True
        Call M3
    End If

    If ActiveSheet.CB("CB4").Value = 1 Then
        AtLeastOneRan = True
        Call M4
    End If

    If ActiveSheet.CB("CB5").Value = 1 Then
        AtLeastOneRan = True
        Call M5
    End If

    If ActiveSheet.CB("CB6").Value = 1 Then
        AtLeastOneRan = True
        Call M6
    End If
    If Not AtLeastOneRan Then MsgBox "Please select at least one option to proceed."
End Sub

edited Nov 3, 2015 at 3:23

answered Nov 3, 2015 at 2:53

sous2817

3,9602 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wmab Over a year ago

Would you need to do an elseif with all 6 options with .value = 0 to get a message box to appear if all these fail?

sous2817 Over a year ago

@wmab see my edit. There are quite a few ways to do this, but I think this is probably the simplest and easiest to follow what's going on.

wmab Over a year ago

Thanks @sous2817 that works! I have to update ActiveSheet to Sheets("Sheet 1") as the macros go to different sheets so wouldn't find the check boxes its looking for on the then activesheet but works well and a lot less lines than my first attempt!

sous2817 Over a year ago

@wmab glad I could help and I'm happy to hear you've got it working! Like I said, there are ways you can trim down the code, but I wanted to keep it simple enough for you to understand and modify (assuming you're new-ish to VBA). Either way, glad you got it sorted out and best of luck with the rest of your project!

PatricK · Accepted Answer · 2015-11-03 08:34:39Z

1

If the checkbox name and the sub name are related 1:1, there is a much better way then creating so many IF conditions.

Consider these checkboxes named beginning with "CB":

With codes in Module1:

Option Explicit

Private Const PREFIX As String = "Module1.M" ' <-- Change this to match your Module Name and Prefix of the Sub Names

Sub LoopCheckboxes()
    Dim sRun As String, oChkBox As Object
    For Each oChkBox In ActiveSheet.CheckBoxes
        With oChkBox
            Debug.Print "Checkbox name: " & .Name
            If .Value = 1 Then
                sRun = PREFIX & Mid(.Name, 3)
                Debug.Print "sRun: " & sRun
                Application.Run sRun
            End If
        End With
    Next
End Sub

Sub M1()
    Debug.Print "M1()"
End Sub

Sub M2()
    Debug.Print "M2()"
End Sub

Sub M3()
    Debug.Print "M3()"
End Sub

When you execute the LoopCheckBoxes, you get:

answered Nov 3, 2015 at 8:34

PatricK

6,4231 gold badge23 silver badges25 bronze badges

2 Comments

wmab Over a year ago

Hey thanks @PatricK! They aren't exactly labeled this way but I could easily update them to be. I need to build out a few macros to do different things based on these selections so I'll try your approach next time, thanks a lot!

PatricK Over a year ago

Welcome @wmab. By the way, try not use that checkbox name convention as "CB1" is a valid cell address. Perhaps "CB_1" is better approach.

Collectives™ on Stack Overflow

Using loop to run certain macros in Excel based on checked check boxes

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related