mercredi 1 novembre 2017

combine multiple checkbox & textbox values from userform/vba into a single cell in excel

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!




Aucun commentaire:

Enregistrer un commentaire