VBA Checkbox range to Table

Question

I have a form with 13 checkboxes. I have it setup so that if it is checked, a cell is marked as either TRUE or FALSE.

What I'm trying to do is export the data from the form into a table. I have everything else finished, however I'm confused on how to input the checkbox values better.

Rather than doing this if statement for all the checkbox's

If CHECKBOX = TRUE Then
    CHECKBOX_VAL = string
End If

If CHECKBOX2 = TRUE Then
    CHECKBOX_VAL2 = string2
End If

table_object_row.Range(x,x).Value = CHECKBOX_VAL
table_object_row.Range(x,x).Value = CHECKBOX_VAL2

How can I simply export the TRUE values of the checkbox to the table. Should I be using an array to store only the True values?

score 0 · Answer 1 · answered Jul 10 '18 at 19:42

You can loop through the controls on your userform, and if the element is of type CheckBox then place in your array, table, or whatever.

Dim ctl
For Each ctl In UserForm1.Controls  'the controls collection of your userform
    If TypeName(ctl) = "CheckBox" Then

        Debug.Print "The value of " & ctl.Name & " is " & ctl.Value
        '(do something with your values here)

    End If
Next ctl

More Information:

Stack Overflow : Overview of differences between Form Controls & ActiveX Controls
MSDN : Controls Collection (Microsoft Forms)
ExcelEasy : Controls Collection
ozgrid : Loop Through Controls on a UserForm

VBA Checkbox range to Table

1 Answers1

More Information: