How to detect if multiple checkboxes are selected

[excelnoob001]

I am creating an userform that have checkboxes users can tick off, and then press select files.

I already have working code that corresponds to each of the checkboxes users can select, however I want to know if there's an efficient way to do this process.

For example, let's say user wants to check off checkboxes A and B, then press select files. This will enable the macro to go to sheets A and B and extract data from a source file onto those sheets.

However, what if the user wants to select many checkboxes? (I have 9 checkboxes total)

My solution was to simply have countless if loops that will deal with every possible combination of checkboxes a user can select, which is like 9 factorial.
(9x8x7x6x5x4x3x2) However, that obviously would be too tedious and not efficient.

Is there an efficient way to create an if loop that will encapsulate every possible combination of checkboxes a user can select?

Thank you!

 

[Yongle]

Perhaps something like this

Private Sub CommandButton_SelectFiles_Click()
    Dim ctrl As Control, List As String, N As String, Q As Variant
    List = ","
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then
                N = ctrl.Name
                List = N & "," & List
            End If
        End If
    Next ctrl
    List = Replace(List, ",,", " ")

    
[COLOR=#006400][I]'loop throught the list like this[/I][/COLOR]
    For Each Q In Split(List, ",")
        MsgBox Q
    Next
End Sub

A useful trick to simplify going from name of checkbox directly to name of sheet

Name checkboxes to mirror sheet names ( cb_SheetA, cb_SheetB, etc )

[COLOR=#006400][I]'looping sheets is now[/I][/COLOR]
    For Each Q In Split(List, ",")
        [I]Do something wit[/I]h [COLOR=#ff0000]Sheets(Replace(Q,"cb_" , ""))[/COLOR]
    Next

- if sheet names contain spaces ( Sheet A, Sheet B, etc)
- name checkboxes to mirror sheet names using an underscore to replace every space ( cb_Sheet_A, cb_Sheet_B, etc )

[COLOR=#006400][I]
[COLOR=#006400][I]'looping sheets is now[/I][/COLOR]
'remove cb_ and then replace [/I][/COLOR][COLOR=#ff0000][I]_[/I][/COLOR][COLOR=#006400][I] with space
[/I][/COLOR]
    For Each Q In Split(List, ",")
        [I]Do something with[/I] [COLOR=#ff0000]Sheets(Replace(Replace(Q,"cb_" , ""), "_" , " "))[/COLOR]
    Next

 

[excelnoob001]

Hi Yongle, thank you for the quick response.

It works!

 

[excelnoob001]

Hi Yongle,

Your macro works perfectly! However, since each worksheet requires a different macro, a For Each loop wouldn't work.

How can I extract worksheet names from the List array and set every worksheet name to a variable?

For example:
Split(List, ",")
set sheet1 = Sheets(Replace(Worksheet_A, "_", " ")) 'var sheet1 now contains "Worksheet A"
set sheet2 = Sheets(Replace(Worksheet_B, "_", " ")) 'var sheet2 now contains "Worksheet B"

Thank you!

 

[Yongle]

What is the name of the checkbox which refers to Sheet A ? what is the actual name of Sheet A
same 2 questions for each of Sheet B and Sheet C

 

[excelnoob001]

Sheet_A
Sheet_B
Sheet_C

are the names of the checkboxes.

I changed your code because I didn't want the checkboxes to be named cb_Sheet_A, as it's not user-friendly.

 

[Yongle]

Sheet & checkbox names:

Sheet A Sheet_A
Sheet B Sheet_B
Sheet C Sheet_C
etc

This code works for me

Private Sub cb_SelectFiles_Click()
    Dim Ctrl As Control, Q As Variant, ws As Worksheet, N As String, List As String
    List = ","
[I][COLOR=#006400]'loop through controls on userform, create string of names of selected checkboxes[/COLOR][/I]
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "CheckBox" Then
            If Ctrl.Value = True Then
                N = Ctrl.Name
                List = N & "," & List
            End If
        End If
    Next Ctrl
    [COLOR=#006400][I]
'convert string to provide [B]EXACT [/B]name of each sheet ( each name is separated by comma )[/I][/COLOR]
    List = Replace(Replace(List, ",,", ""), "_", " ")
    
[I][COLOR=#006400]'loop throught the list like this[/COLOR][/I]
    For Each Q In Split(List, ",")
        Set [COLOR=#ff0000]ws[/COLOR] = Sheets(Q)
        Select Case Q
            Case "Sheet A"
                MsgBox "do what's required to Sheet A"
                [COLOR=#ff0000]ws[/COLOR].Cells(1, 1) = "value in A1"
            Case "Sheet B"
                MsgBox "do what's required to Sheet B"
                [COLOR=#ff0000]ws[/COLOR].Cells(1, 1) = "value in A1"
            Case "Sheet C"
                [COLOR=#006400][I]' etc[/I][/COLOR]
            Case "Sheet D"
            
            Case "Sheet E"
            
            Case "Sheet F"
            
            Case "Sheet G"
            
            Case "Sheet H"
            
            Case "Sheet I"

        End Select
    Next Q
End Sub

 

[excelnoob001]

Thank you Yongle!

Everything works now