How to make checkboxes (form or Active X) and return all checked names into an array?

[wiseone]

Hi all,

I have a bit of code which extracts data from source files. I'd like to be able to select checkboxes to extract the data from several files at once by choosing checkboxes.

Each checkbox will correspond to a file name.

I prefer form control because it seems easier to setup, but I'm not opposed to activeX if needed.

How do I read all the names of the checked boxes into an array when a macro is executed?

Example:

Checkboxes:

Name 1 - Checked
Name 2 - Checked
Name 3 - Not Checked
Name 4 - Checked
Name 5 - Not Checked

The result would be:

Dim cbSelections as Variant
cbSelections = array("Name 1", "Name 2", "Name 4")

Thanks in advance!

 

[Domenic]

The following macro assumes that the sheet containing the checkboxes is the active sheet...

Sub test()

    Dim aCheckBoxNames() As String
    Dim oChBx As CheckBox
    Dim ChBxCnt As Long
    Dim i As Long
    
    With ActiveSheet
        ReDim aCheckBoxNames(1 To .CheckBoxes.Count)
        ChBxCnt = 0
        For Each oChBx In .CheckBoxes
            If oChBx.Value = xlOn Then
                ChBxCnt = ChBxCnt + 1
                aCheckBoxNames(ChBxCnt) = oChBx.Name
            End If
        Next oChBx
        ReDim Preserve aCheckBoxNames(1 To ChBxCnt)
    End With
    
    For i = LBound(aCheckBoxNames) To UBound(aCheckBoxNames)
        Debug.Print aCheckBoxNames(i)
    Next i
    
End Sub

Hope this helps!

 

[wiseone]

thank you! Is this for form or active x? (Sorry new at using anything other than a button to start macros)......and if there are no checkboxes checked...will it cause an error?

 

[wiseone]

Hi...also I just tried this and it did not work....it captured the checkbox name, but not the text associated with the checkbox that the user sees.....do you know how to capture that instead?

 

[Domenic]

thank you! Is this for form or active x? (Sorry new at using anything other than a button to start macros)......

It's for checkboxes from Form controls...

...and if there are no checkboxes checked...will it cause an error?

Yes, sorry, I've amended the code to deal with it, and I've amended it to capture the caption instead of the name...

Sub test()

    Dim aCheckBoxNames() As String
    Dim oChBx As CheckBox
    Dim ChBxCnt As Long
    Dim i As Long
    
    With ActiveSheet
        ReDim aCheckBoxNames(1 To .CheckBoxes.Count)
        ChBxCnt = 0
        For Each oChBx In .CheckBoxes
            If oChBx.Value = xlOn Then
                ChBxCnt = ChBxCnt + 1
                aCheckBoxNames(ChBxCnt) = oChBx.Caption
            End If
        Next oChBx
    End With
    
    If ChBxCnt > 0 Then
        ReDim Preserve aCheckBoxNames(1 To ChBxCnt)
        For i = LBound(aCheckBoxNames) To UBound(aCheckBoxNames)
            Debug.Print aCheckBoxNames(i)
        Next i
    Else
        MsgBox "No checkboxes were checked...", vbInformation
    End If
    
End Sub

 

[wiseone]

excellent! Thanks again!

 

[Domenic]

You're very welcome!

Cheers!