Looping through Checkboxes in Userform to gather Caption names

geekgirl613

New Member
Joined
Sep 10, 2014
Messages
6
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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:

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

New Member
Joined
Sep 10, 2014
Messages
6
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:

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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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

New Member
Joined
Sep 10, 2014
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Simple example using TypeName so you don't have to rely on control names:

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

New Member
Joined
Sep 10, 2014
Messages
6

ADVERTISEMENT

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:

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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

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