CODEWITHSUNDEEP
vbaexcel
wmab
wmab

Reputation: 25

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

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.

Upvotes: 0

Views: 973

Answers (2)

PatricK
PatricK

Reputation: 6433

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":
CheckBoxes

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:
Output

Upvotes: 1

sous2817
sous2817

Reputation: 3960

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

Upvotes: 1

Related Questions