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!

 

[bryanivad]

That is correct.
I didn't know if I needed to adjust anything other than the range to put the results in column D

 

[Norie]

The code I posted puts the results in 2 columns A & B, A is where the checkbox caption(s) go and B is where the value(s) from the textbox(es) go, starting at row 1.

If you want everything in column D then the code will need altered.

 

[bryanivad]

Okay. I'll see if I can alter the other things on the form once I've plugged this code in and see how it works. Initially I was going to attempt to string all ticked checkbook captions and their textbox quantities into a single cell, so that each row contains 1 test by 1 employee. What you've done here may be a better approach, I'll just have to see how an entry reacts before I know for sure. Thank you so much for your help on this. It is truly appreciated.

 

[bryanivad]

I tested this out. It works beautifully.... until I attempt to make another entry--then it enters the new mistakes over the original ones. Perhaps if I explain a bit more it will help.
This worksheet is just one of many. Each Supervisor/Manager has their own worksheet to grade the tests that new trainees must complete to be certified for our work. There will literally be hundreds of entries on each sheet, as each employee has hundreds of tests they must complete. Originally we used our sheets to type out the date, the new employees' user name, the test id, and would write out the mistakes they made into a single cell and then 2 upper managers would be responsible for scoring all of the tests. The problem was--each person had their own way of explaining the mistakes and sometimes important errors could get lost in translation with left us with a flawed point deduction system. This userform that I'm working on is an attempt to streamline that process, so that each mistake is clearly stated. What you have done with making each mistake go into a new row and each quantity go into a new column is very useful--because now I can likely find an equation to have the points automatically deducted for the upper managers to make this process even easier on them.

As of now my only problem with what this is doing is that each entry starts over at D2 instead of going into the next empty row so that more than 1 test can be entered onto a worksheet. Thank you again for all of your help.

 

[Norie]

That's an easy fix, and I kind of figured that would want the results to go into the next empty row.

Can you post the code you currently have and I'll update it later?

 

[bryanivad]

Sure thing! Thank you so much.

Private Sub cmdbutton_NextL2R_Click()
 
Dim emptyRow As Long
 
'Make Sheet1 active
Sheet1.Activate
 
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
 
'Transfer information
Cells(emptyRow, 1).Value = TextBox_Date
Cells(emptyRow, 2).Value = TextBox_SQC
Cells(emptyRow, 3).Value = TextBox_JobID
Cells(emptyRow, 6).Value = TextBox_Other

'Input Mistakes & Quantities
Dim arrResults()
Dim I As Long
Dim cnt As Long

    For I = 1 To 39
        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 D2
    Range("D2").Resize(UBound(arrResults, 2) + 1, UBound(arrResults, 1)).Value = Application.Transpose(arrResults)

I am having a problem getting my other textboxes (Date, SQC, JobID, Other) to respond correctly to having multiple rows between the next entry as well. What I had worked perfectly when my results were in a single cell, but now that they're in multiple cells across multiple rows, my emptyRow won't compute correctly. If there is a way to automatically merge them by determining how many mistake rows are in each entry--that would be ideal. So that if they made 4 different mistakes, the date would be merged down those four rows, SQC, Job ID, and Other as well. (I'm not requesting help on that too necessarily, just letting you know that I realize my emptyRow/transfer information sections as they are in my code above won't work with this.)

 

[bryanivad]

Hey Norrie,

I updated my code a little bit. Thought doing it this way might lend itself better to what I'd like to do with my other textboxes. I'm still having trouble figuring out how to get new entries to be listed below instead of overriding everything starting at D2. If you could help me out with that, everything should be wonderful. Thanks again!

Private Sub cmdbutton_NextL2R_Click()
 
Dim emptyRow As Long
 
'Make Sheet1 active
Sheet1.Activate
 
'Make CheckBoxes compile into mistakes cell
Dim arrResults()
Dim I As Long
Dim cnt As Long
 
    For I = 1 To 39
        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 D2
Range("D2").Resize(UBound(<wbr>arrResults, 2) + 1, UBound(arrResults, 1)).Value = Application.Transpose(<wbr>arrResults)
 
With Sheet1
    emptyRow = .Range("D" & Rows.Count).End(xlUp).Row
    .Range("A" & emptyRow) = TextBox_Date.Text
    .Range("B" & emptyRow) = TextBox_SQC.Text
    .Range("C" & emptyRow) = TextBox_JobID.Text
End With
 
'Empty the form when Next L2R is clicked
Call UserForm_L2RRita_Initialize
 
End Sub

 

[Norie]

Try this.

With Sheet1
    emptyRow = .Range("D" & Rows.Count).End(xlUp).Row
    .Range("A" & emptyRow) = TextBox_Date.Text
    .Range("B" & emptyRow) = TextBox_SQC.Text
    .Range("C" & emptyRow) = TextBox_JobID.Text
    .Range("D" & emptyRow).Resize(UBound(arrResults, 2) + 1, UBound(arrResults, 1)).Value = Application.Transpose(arrResults)
End With

 

[bryanivad]

That did it. Perfect! Thank you so much!