Combine userform checkbox & textbox into a string/loop in a single cell

[bryanivad]

Hello there. As it is probably obvious, I am super green on attempting VBA. But since I have some time, I have been exploring it to streamline processes at work. Here is a small background about my dilemma:
My userform is going to be used to grade employee training with the results being added to an excel spreadsheet. The checkboxes in my form are representative of the different kinds of mistakes they can make and each mistake ideally has a quantity box that will specify how many times each mistake was made if its box is checked. I need both the checkbox caption and the textbox value in one cell, as each row is 1 test for each employee.

Here is a portion of my form as an example:

So far I was able to use a loop with a string to get the checkboxes to put their captions into the single cell using this code:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim CheckBox As Control
Dim Mistakes As String, delimiter As String

For Each CheckBox In Me.Frame_Mistakes.Controls
If TypeOf CheckBox Is MSForms.CheckBox Then
If (CheckBox.Value) Then
Mistakes = Mistakes & delimiter & CheckBox.Caption
delimiter = " | "
End If
End If
Next

With Sheet1
.Cells(emptyRow, 4).Value = Mistakes
End With</code>
And I understand that in order to get my output to look like this:

That I need to edit my Mistakes expression to say something like:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Mistakes = Mistakes & delimiter & Checkbox.Caption & "(x" & TextBox.text & ")"</code>
However, I'm not entirely sure how to get my checkboxes to acknowledge their respective textboxes so that the output captions are with the textbox that is assigned to it. I would also like to limit the character size of the textbox to be 2 and force it to only allow numbers (I assume this will be some kind of isnumeric expression).

I have ensured that the checkbox number matches the textbox number for each mistake on my userform (if that helps).

If anyone could offer some help, I would be forever grateful!

 

[mikerickson]

I would use a loop like

Dim cont as Ms.Forms.Control
Dim Result as String
Dim Delimiter as String

Delimiter = "|"

For each cont in Me.Frame_Mistakes.Controls
    If Typename(cont) = "CheckBox" Then
        If cont.Value Then Result = Result & Delimiter & cont.Caption
    End If
Next cont

Result = Mid(Result, Len(Delimiter) + 1)

 

[bryanivad]

Thank you for the quick reply.

When I tried this it gave me a compile error: user-defined type not defined.

 

[mikerickson]

My mistake. It should be

Dim cont as MsForms.Control

 

[bryanivad]

Thank you again! That is easier to look at than what I was using previously, but this code does what my previous code was already doing (stringing the checkbox captions into the single cell). The textboxes that are supposed to give the quantity of the mistakes is where I'm getting stuck. I'm not sure how to get each textbox to be part of the string with the checkbox caption in the single cell output.

 

[Norie]

How are the checkboxes and textboxes named?

Is there a pattern that can be used so that the correct textboxes and checkboxes are combined?

i.e. the checkbox for a specific class of mistake will be matched with the textbox for the quantity of errors for that class of mistake

 

[bryanivad]

Yes. Thank you! I just simply named them i.e. CheckBox1 , TextBox1 etc. respectively. I assumed that would make the goal easier to achieve, but my logic kind of ended there.

 

[Norie]

You really do need something to help you match up the checkboxes and textboxes to get the correct results.

Let's say the checkboxes are named CheckBox1, CheckBox2... and the corresponding textboxes are named TextBox1, TextBox2, ... then we could use something like this.

Dim arrResults()
Dim I As Long
Dim cnt As Long

    For I = 1 To 5
        If Me.Controls("CheckBox" & I).Value = True Then
            ReDim Preserve arrResults(1 To 2, cnt)
            arrResults(1, cnt) = Me.Controls("CheckBox" & I).Caption
            arrResults(2, cnt) = Me.Controls("TextBox" & I).Value
            cnt = cnt + 1
        End If
    Next I

    ' put results on active sheet, starting in A1

    Range("A1").Resize(UBound(arrResults, 2) + 1, UBound(arrResults, 1)).Value = Application.Transpose(arrResults)

 

[bryanivad]

Wow. That is very impressive. I will try it out tomorrow at work. I really appreciate this, thank you. Quick question before I try it: their mistake results will be in column D. Is there anything I would need to change other than adjusting the range in that last line of code?

 

[Norie]

Do you mean you want the results in column D?