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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
35,520
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
35,520
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,435
Messages
5,528,746
Members
409,833
Latest member
tdnhan

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top