Looping through Checkboxes in Userform to gather Caption names

[geekgirl613]

Hello Everyone, this is my first time on this site, so I hope I provide enough detailed information. I am working on a vba userform that I need to check to see if checkboxes are checked and I need to gather the caption name, if they are. I am having a difficult time with iterating through the checkboxes. I have figured out how to do it one by one, but I have quite a few checkboxes to go through. Thanks!

 

[NeonRedSharpie]

I have just finished a battleship userform so I could learn ALL about checkboxes/option buttons/text boxes/ what have you. So here's how I did it:

    For Each myFrame In Me.Controls        
        If Left(myFrame.Name, 8) = "CheckBox" Then
            myFrame.Enabled = True
            myFrame.BackColor = &H8000000F
        End If
    Next

Of course my check boxes were all CheckBox1, CheckBox2.....CheckBox100

 

[geekgirl613]

My question here is how would I use this loop to capture each caption name because there is no index. Also, is there a book or source that is really good at describing all of the functions of checkboxes.

I have just finished a battleship userform so I could learn ALL about checkboxes/option buttons/text boxes/ what have you. So here's how I did it:

    For Each myFrame In Me.Controls        
        If Left(myFrame.Name, 8) = "CheckBox" Then
            myFrame.Enabled = True
            myFrame.BackColor = &H8000000F
        End If
    Next

Of course my check boxes were all CheckBox1, CheckBox2.....CheckBox100

 

[NeonRedSharpie]

What are your checkbox names and where do you want to store them? I don't mean the caption names, I mean the actual names?

 

[geekgirl613]

My checkbox names are just "CheckBox1, CheckBox2, etc." I would love to be able to do a string array, if possible.

What are your checkbox names and where do you want to store them? I don't mean the caption names, I mean the actual names?

 

[RoryA]

Simple example using TypeName so you don't have to rely on control names:

    Dim ctl                   As MSForms.Control
    Dim n                     As Long
    Dim myArray()             As String

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            If ctl.Value = True Then
                ReDim Preserve myArray(n)
                myArray(n) = ctl.Caption
                n = n + 1
            End If
        End If
    Next ctl

    MsgBox myArray(0)

 

[geekgirl613]

Thank you so much for your help, but I have one more question. Can I create a sub to start at ANY click event (mainly for any checkbox) as I do not know what the user will click first?

Simple example using TypeName so you don't have to rely on control names:

    Dim ctl                   As MSForms.Control
    Dim n                     As Long
    Dim myArray()             As String

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            If ctl.Value = True Then
                ReDim Preserve myArray(n)
                myArray(n) = ctl.Caption
                n = n + 1
            End If
        End If
    Next ctl

    MsgBox myArray(0)

 

[NeonRedSharpie]

Thank you so much for your help, but I have one more question. Can I create a sub to start at ANY click event (mainly for any checkbox) as I do not know what the user will click first?

It's probably smarter to have a button at the end and collect it all once that button is pressed. That way you only have one set of looping through the checkboxes. Plus, you won't be overwriting arrays when the second and third check boxes are checked.

 

[RoryA]

I agree with NeonRedSharpie but for reference, you can handle the click event of every checkbox using a class module. There are many examples on this forum - for example post #4 on this thread.