Add code that will recognize dynamically created labels in userform

[TheSecretaryJen]

Hi all,

I have built a userform for recording training records. On the first page, there is a combobox and three command buttons, plus an empty frame and a hidden textbox called "Count."

To add names:
1. Users select a name in the combobox list,
2. and click the command button that says "Add."
3. If the combobox is empty, the code exits.
4. If the combobox has a name selected that has already been entered, a warning box pops up and the code exits.
5. If there are already fifty names in the frame, a warning box pops up and the code exits.
6. Once all these checks have been performed, the "Count" textbox, which is set to a default of 0, counts up by 1. This enables the code to know what number label to add next.
7. The code then creates a new label with the selected name as the caption. It is named "Name" and whatever the count number is, i.e. "Name1."
8. The code positions the label inside the frame, next to an existing checkbox called "Remove1," "Remove2," etc., which has been hidden but which is now made visible by the code. The numbers correspond with one another: Name1 is paired with Remove1.

To remove names:
1. The users check the checkboxes next to the names they wish to remove,
2. and click the "Remove" command button.
3. The code checks to make sure that at least one box has been checked,
3. and then figures out which Name labels are next to the checked Remove checkboxes by matching the numbers.
4. Then it deletes the chosen labels,
5. figures out how many labels remain,
6. resets the Count textbox accordingly,
6. renames all the remaining labels with fresh numbers (i.e. instead of Name1, Name4, Name7, it would become Name1, Name2, and Name3),
7. rearranges the labels so they correspond with the correct checkboxes,
8. and hides all checkboxes that are no longer needed.

I know that sounds very convoluted, but it works very well and runs fast, and it was the only way I could think of to add multiple names to a list and arrange them correctly, plus being able to remove any names in any location and have them automatically rearrange nicely.

Here is the problem: once all the names have been chosen, I want the user to be able to click a command button called "Enter Record" and run code that copies all the captions in the labels and pastes them into a table in my spreadsheet. The table is called "TrainingRecords." The names would go in the very first column, which is column A.

The code I wrote works perfectly well when the labels are there at design time. However, no matter what I try, I can't get the code to recognize the labels that are created from this runtime code. It isn't that the code crashes. It's that it simply doesn't do anything. Is a class module required? I have no idea how to write one, so if that is what's needed, please explain every single step to me.

Here is the code I have already written. I am aware that it probably is a mess, but it works and I fought with it for two weeks to make it work, so I'm okay with it being messy.

To Enter Names:

Private Sub AddNames_Click()
If Me.Count.Value = 50 Then
MsgBox "You have already selected the maximum number of names." & vbNewLine & "Please either remove a name or continue with your current selections.", vbCritical
Exit Sub
End If
If Me.AddName.Value = "" Then
Exit Sub
End If
Dim contr As Control
For Each contr In Me.Frame1.Controls
    If TypeName(contr) = "Label" Then
        If contr.Caption = Me.AddName.Value Then
        MsgBox "You have already selected that name. Please choose another one.", vbCritical
        Exit Sub
        End If
    End If
Next
Me.Count.Value = Me.Count.Value + 1
Dim i As Integer
Dim aName As Control
Dim aCheck As Control
i = Me.Count.Value
Set aCheck = Me.Controls("Remove" & i)
    Set aName = Me.Frame1.Controls.Add("Forms.Label.1")
    With aName
    .Name = "Name" & i
    .Top = aCheck.Top
    .Left = aCheck.Left - 108
    .Caption = Me.AddName.Value
    .BackStyle = fmBackStyleOpaque
    .Height = 10
    .Width = 102
    .BackColor = &H80000016
    End With
aCheck.Visible = True
End Sub

To Delete Names:

Private Sub ClearCheckedBoxes_Click()
Dim i As Integer
Dim b As Integer
Dim contr As Control
Dim x As Integer
Dim y As Integer
Dim Flag As Boolean
Dim MyCount As Integer
x = Me.Count.Value
MyCount = 0
i = 1
Flag = True
For Each contr In Me.Frame1.Controls
    If TypeName(contr) = "CheckBox" Then
        Flag = Flag And (contr.Value = False)
    End If
Next
If Flag Then
    Exit Sub
End If
If MsgBox("Clear checked names?", vbYesNo) = vbNo Then
Exit Sub
End If
For b = 1 To x
    For Each contr In Me.Frame1.Controls
        If contr.Name = ("Remove" & b) Then
            If contr.Value = True Then
                Me.Frame1.Controls.Remove ("Name" & b)
            End If
        End If
    Next contr
    For Each contr In Me.Frame1.Controls
        If contr.Name = "Name" & b Then
            MyCount = MyCount + 1
        End If
    Next contr
Next b
For b = 1 To x
    For Each contr In Me.Frame1.Controls
        If contr.Name = "Name" & b Then
            contr.Name = "Name" & i
            i = i + 1
        End If
    Next
Next b
x = MyCount
For b = 1 To x
    For Each contr In Me.Frame1.Controls
        If contr.Name = "Name" & b Then
            contr.Left = Me.Frame1.Controls("Remove" & b).Left - 108
            contr.Top = Me.Frame1.Controls("Remove" & b).Top
        End If
    Next
Next b
Me.Count.Value = MyCount
For Each contr In Me.Frame1.Controls
    If TypeName(contr) = "CheckBox" Then
        contr.Value = False
    End If
Next
For b = x + 1 To 50
    For Each contr In Me.Frame1.Controls
        If contr.Name = "Remove" & b Then
            contr.Visible = False
        End If
    Next
Next b
End Sub

To Paste to the Table:

Private Sub EnterRecordButton_Click()
Dim lRow As Long
Dim ws As Worksheet
Dim contr As MSForms.Control
Dim i As Integer

Set ws = Worksheets("Training Records")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For Each contr In Me.Frame1.Controls
    If contr.Name = "Name" & i Then
        ws.Cells(lRow - 1 + i, 1).Value = contr.Value
    End If
Next contr

End Sub

 

[TheSecretaryJen]

I solved it. I just added code into the "Enter Names" code that copies the name and inserts it into a table. Then in the "Clear Names" code, I match the selected names with the names in the table and delete the table row. Works like a charm, and now I can reference the table list instead of the labels directly.