Excel Userform/VBA Combine CheckBox & TextBox into a single cell string

Question

A little background about what I'm trying to do: 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 in one cell, as each row is 1 test for each employee. Here is an example of my userform: Mistakes Screen Shot

All checkboxes and textboxes share the same number and I've already programmed them to automatically insert a 1 quantity if the box is checked/empty if unchecked. (Of course the quantity must be editable in case a mistake type is duplicated.)

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:

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

However, so far I have been unable to figure out how to get the quantity to be added at the end of each mistake preceding the delimiter. I would prefer it if I could get the string to be in this format: Mistakes Format in Excel

If my intentions are unclear, I apologize. I am incredibly new and honestly surprised I was able to make it this far. Please and Thanks!

JustinCoplin · Accepted Answer · 2017-11-02 12:18:45Z

0

So, if I understand you correctly, You want the output of the Mistakes cell to read

[Caption of checked box](x[Number in Text Box])|[Caption of checked box](x[Number in Text Box])...

If that is so, you simply need to add a snippet of code to the end of your 'Mistakes' variable so that it reads:

Mistakes = Mistakes & delimiter & Checkbox.Caption & "(x" & TextBox.text & ")"

Where things get difficult is that you will need to differentiate between text boxes so that only the one that applies to the relevant checkbox is being used. You could do this in a number of ways, such as passing the Textbox as an argument, or with a switch case to name a few.

Another problem is making sure that the textbox only uses numbers. The way that I accomplish this is with a combination of the IsNumeric() and Val() functions. You first check if the value is numeric, then store it in an int using the Val() function. Since you only need it for a string, though, using the IsNumeric() function alone should be fine.

If you need more specific clarification, I would need to know exactly what you are looking for.

answered Nov 2, 2017 at 12:18

JustinCoplin

1693 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BryanivaD Over a year ago

Thank you so much for replying! The output example you gave is exactly what I'm looking for. I added the snippet you provided and it's giving me an error. I imagine this is because the textboxes aren't differentiated yet. I am going to research the isnumeric and passing the textbox as an argument options and try to make this work. Though, admittedly it's like a shot in the dark. I sincerely appreciate your guidance, as this is a giant leap in the right direction.

Collectives™ on Stack Overflow

Excel Userform/VBA Combine CheckBox & TextBox into a single cell string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related