How to detect if multiple checkboxes are selected

excelnoob001

New Member
Joined
Jul 24, 2019
Messages
26
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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,973
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this
Code:
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 )

Code:
[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 )
Code:
[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
 
Last edited:

excelnoob001

New Member
Joined
Jul 24, 2019
Messages
26
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,973
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 24, 2019
Messages
26
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,973
Office Version
  1. 365
Platform
  1. Windows
Sheet & checkbox names:

Sheet A
Sheet_A
Sheet B Sheet_B
Sheet C Sheet_C
etc

This code works for me

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,158
Messages
5,594,588
Members
413,915
Latest member
namreh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top