Loop to extract value from checkbox

Question

A form I am working with has 10 checkboxes, with values 1 through 10, used to answer a multiple choice question.

Multiple values are technically possible (clicking on multiple boxes), but they are not allowed (while filling, only one value should be given). I cannot modify this form so I have to work with this setup.

I need to extract the given choice and paste it into a different worksheet. Using this question I can extract the value of every single checkbox and develop a IF Loop.

If ExtractionSheet.Shapes("Check Box 1").OLEFormat.Object.Value = 1 Then

Database.Cells(5, 9).Value = 1

ElseIf ExtractionSheet.Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then

Database.Cells(5, 9).Value = 2

ElseIf ExtractionSheet.Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then

Database.Cells(5, 9).Value = 3

...

However, this does not look very efficient (I have 3 sets of 1-10 checkboxes per form and 100+ forms). Given the setup, I cannot figure out a better way to do it.

How can I improve the extraction without using an IF loop?

EDIT A better description of the form, following comments

This is a simple excel worksheet, in which 3 groups of 10 check box elements were pasted.

Each form/worksheet relates to a single item. During the assessment, for each item we will assign a value between 1 and 10 to Property 1 (first 10 check boxes), a value between 1 and 10 to Property 2 (second 10 check boxes) and a value between 1 and 10 to Property 3 (third 10 check boxes).

I will do the filling (physically clicking the box) while in front of the client who is giving me data to fill it. The possibility of clicking multiple boxes naturally exists; I do not think it will be critical because many people will be looking at the screen while I do it, but I can always add a check later on.

An `If` is not a loop - It is a statement. A `For` or `Do` is used for a loop — Tom, Jan 02 '19 at 09:30
Could you add an event on the form so that if another is selected the previous one is auto unselected? — Tom, Jan 02 '19 at 09:38
@Tom No. But I could check during the extraction if there are two or more variables selected and issue a warning. — laureapresa, Jan 02 '19 at 09:45
@SJR Yes, they are on an Excel user form that my client provided and that will fill out with me. I will actually do the filling (box clicking etc.) — laureapresa, Jan 02 '19 at 09:47
@laureapresa If they are on a userform where is the initial information coming from? — Tom, Jan 02 '19 at 09:48
@Tom I edited the question to include a better explanation of the form structure — laureapresa, Jan 02 '19 at 10:02

Tom · Accepted Answer · 2019-01-02T12:09:15.913

Updated after comments:

I have used the following naming convention for the checkboxes (Using just e.g. A1 is a cell reference and could cause problems)

ChkBox_A1

Where the first part denotes that it is a checkbox (ChkBox), second the group A and third the position 1. With this naming convention and how the code is currently written you will be able to have a maximum of 26 groups (i.e. one for every letter of the alphabet)

I use the immediate window for the results which can be accessed in the VBA editor by going to View->Immediate Window or Ctrl+G

This code will handle single select per group. i.e. If a checkbox is selected in the group it will un-select all other ones

For a worksheet

This code goes in the worksheet object

Replace all of the click statements (e.g. ChkBox_A1_Click() with reference to your own. This can be easily done by calling the GenerateChkBoxClickStmt sub and copying and pasting the output in the immediate window into your code (replacing my ones)

Option Explicit
Dim ChkBoxChange As Boolean
Private Sub ChkBox_A1_Click()
    If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
    If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
    If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub UnselectPreviousChkBox(selected As Object)
    Dim ChkBox As OLEObject

    ChkBoxChange = True

    For Each ChkBox In Me.OLEObjects
        If ChkBox.progID = "Forms.CheckBox.1" Then
            If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
                ChkBox.Object.Value = False
            End If
        End If
    Next ChkBox

    ChkBoxChange = False
End Sub
Private Sub GenerateChkBoxClickStmt()
    Dim ChkBox As OLEObject
    ' Copy and paste output to immediate window into here

    For Each ChkBox In Me.OLEObjects
        If ChkBox.progID = "Forms.CheckBox.1" Then
            Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
            Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
            Debug.Print "End Sub"
        End If
    Next ChkBox
End Sub

Producing the following:

This code goes into a Module

Option Explicit
Private Function GetChkBoxValues(ChkBoxGroup As Variant) As Long
    Dim ChkBox As OLEObject

    ' Update with your sheet reference
    For Each ChkBox In ActiveSheet.OLEObjects
        If ChkBox.progID = "Forms.CheckBox.1" Then
            If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = ChkBoxGroup Then
                GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
                Exit For
            End If
        End If
    Next ChkBox
End Function
Public Sub GetSelectedChkBoxes()
    Dim ChkBoxGroups() As Variant
    Dim Grp As Variant

    ChkBoxGroups = Array("A", "B", "C")

    For Each Grp In ChkBoxGroups
        Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
    Next Grp
End Sub

By running the GetSelectedChkBoxes the code will output to the immediate window:

For a UserForm

Similarly the statements for the click events can be generated by uncommenting the line in the Userform_Initalize sub

Option Explicit
Dim ChkBoxChange As Boolean
Private Function GetChkBoxValues(Group As Variant) As Long
    Dim ChkBox As Control

    For Each ChkBox In Me.Controls
        If TypeName(ChkBox) = "CheckBox" Then
            If ChkBox.Object.Value = True And Mid(ChkBox.Name, 8, 1) = Group Then
                GetChkBoxValues = Right(ChkBox.Name, Len(ChkBox.Name) - (Len("ChkBox_") + 1))
                Exit For
            End If
        End If
    Next ChkBox
End Function
Private Sub UnselectPreviousChkBox(selected As Control)
    Dim ChkBox As Control
    ChkBoxChange = True
    For Each ChkBox In Me.Controls
        If TypeName(ChkBox) = "CheckBox" Then
            If ChkBox.Name <> selected.Name And Mid(ChkBox.Name, 8, 1) = Mid(selected.Name, 8, 1) Then
                ChkBox.Value = False
            End If
        End If
    Next ChkBox
    ChkBoxChange = False
End Sub
Private Sub ChkBox_A1_Click()
    If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A1
End Sub
Private Sub ChkBox_A2_Click()
    If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_A2
End Sub
Private Sub ChkBox_B1_Click()
    If ChkBoxChange = False Then UnselectPreviousChkBox Me.ChkBox_B1
End Sub
Private Sub userform_initialize()
    ' Comment out once written
    ' GenerateChkBoxClickStmt
End Sub
Private Sub UserForm_Terminate()
    Dim ChkBoxGroups() As Variant
    Dim Grp As Variant

    ChkBoxGroups = Array("A", "B", "C")

    For Each Grp In ChkBoxGroups
        Debug.Print "Group " & Grp, GetChkBoxValues(Grp)
    Next Grp
End Sub
Private Sub GenerateChkBoxClickStmt()
    Dim ChkBox As Control
    ' Copy and paste output to immediate window into here
    For Each ChkBox In Me.Controls
        If TypeName(ChkBox) = "CheckBox" Then
            Debug.Print "Private Sub " & ChkBox.Name & "_Click()"
            Debug.Print vbTab & "If ChkBoxChange = False Then UnselectPreviousChkBox Me." & ChkBox.Name
            Debug.Print "End Sub"
        End If
    Next ChkBox
End Sub

Producing:

and outputting the following on exit:

Not at all. I could rename them to a progressive number and then apply your solution. How would I approach the fact that I have 3 groups of 10 check boxes per form? — laureapresa, Jan 02 '19 at 09:51
How do you group them together? I'd recommend naming them with some sort of logic. If there is nothing grouping them, you could also apply a naming convention to depict which group the checkbox is related to. e.g. "Group A Checkbox 1" etc. — Tom, Jan 02 '19 at 09:58
I can rename my check boxes as A1, A2... A10, B1, B2...B10 and so on.How would I edit your code to run only for one group? — laureapresa, Jan 02 '19 at 10:17
@laureapresa Please have a look at my update. I've produced a more complex answer which will handle single select per group and account for all groups in both a `WorkSheet` method or a `UserForm`. If you have any questions feel free to ask. Please pay attention to the `GenerateChkBoxClickStmt` as these subs will generate all of the `Click` statements for you instead of having to write them (they are based off of the `CheckBox` Names so will need to be structured correctly to work. I've suggested a naming pattern for you as well — Tom, Jan 02 '19 at 12:04

Loop to extract value from checkbox

1 Answers1