Excel VBA Help with dynamic forms!

[Daniel Campos]

Hello everyone and thanks in advance for all attention and help provided!

I'm crawling my way to understand Excel and now I'm facing a challenge: I need to create a dynamic form!

Here's the issue: I have a list of contracts and some workers of a team who work with them. They're having problems sharing the same workbook, so I cannot from start make one workbook for them all to share!

Building up on past experiences I tought of having each worker have its own "client" workbook and have a "server" workbook. From their "client" wb they would have to include new lines with contracts' infos in the "server" wb, and also get lines from contracts other workers put there.

The way I'm trying to solve this is throught forms. Some of them work wonderfully and are somewhat easy to use. The worker from a "client" wb (Cwb) can insert all info in a form and that info goes to the "server" wb (Swb), search one contract's info, and put on another wb for their manager to deal with their work.

The problem comes when I want to create a way for them to select those contracts they want to work with without making the Swb be open for more than a few seconds. I'm struggling specifically with the creation of a dynamic form, that will have 4 textboex and a checkbox for every line of contract, the textboxes to display information and the checkboxes to select those lines of contract the user is gonna work with, that will be copied to his Cwb while in the Swb will be marked as having him as responsible (To help the macro go throught all the lines and see which ones have someone working with them already or not, I have a column that marks who is responsible of that contract and if noone is responsible then that contract can appear on the list).

This is what I have worked out so far:

Private Sub userform_Initialize()
    
    Dim i, p, k As Long
    Dim newTxtBox As Control
    Dim newChkBox As Control
    Dim newOkBut As Control
    Dim newCnlBut As Control
    
    'On Error GoTo EH
    
    'On the sub that I use to open the form I already open the Swb and saved it, and
    'after the form runs its course it saves the Swb and close it.
    
    With Workbooks("Controle BECs").Worksheets("BECs")
    
        i = Range("L2").Value
        
        k = Range("M2").Value
        
        'In the Swb I have two cells with formulas to count the amount of contracts
        'listed in the column A and the amount of contracts with responsible workers in
        'the column M
        
        If i - k <= 0 Then
        
            'The text in the msgbox is in Portuguese. It simply states that if the
            'number of contracts is equal to the number of contracts with responsible
            'workers, there's no contracts to select.
            'And if this is the case, then let's leave this because it is pointless to
            'keep going.
        
            MsgBox "Não há BECs disponíveis na lista de BECs.", vbOKOnly, "Puxar BECs"
            Unload.me
            Exit Sub
        
        End If
        
        'Here I define arrays to store the infos of all contracts without responsible workers
        'attributed to them. I rezise them to the size they actually should have
        
        Dim todosBECs() As Variant      'todosBECs = allContracts
        Dim todasBases() As Variant     'todasBases = allDepartments
        Dim todosClientes() As Variant  'todosClientes = allCustomers
        Dim todosValores() As Variant   'todosValores = allValues
        
        'Each of these arrays store key information I need to provide the user with so
        'she/he/apache helicopter can select the contracts the user will work with.
        'They're contract number, department number, client name, and contract value
        
        i = i - k
        
        ReDim todosBECs(1 To i)
        ReDim todasBases(1 To i)
        ReDim todosClientes(1 To i)
        ReDim todosValores(1 To i)
        
        p = 0
        
        For p = 0 To i
        
        'Here I add the contract information in the corresponding array position if the
        'column for responsible worker is empty
        
            If Cells(p + 1, 13).Value = vbNullString Then
        
                todosBECs(p) = Cells((p + 1), 1).Value
                todasBases(p) = Cells((p + 1), 2).Value
                todosClientes(p) = Cells((p + 1), 3).Value
                todosValores(p) = Cells((p + 1), 6).Value
            
            End If
        
        Next p
        
    End With
    
    p = 0
    
    For p = 0 To i
    
        'Now here is the part I'm struggling with. I need to add as many textboxes and
        'checkboxes as the number of contracts without a responsible worker and store
        'in each of them the corresponding information. Maybe I didn't get the syntax
        'right?
        
        Set newTxtBox = Me.Controls.Add("Forms.TextBox.1", "BEC" & p, True)
                        
        newTxtBox.Name = "BEC" & p
        newTxtBox.Top = 36 + 18 * p
        newTxtBox.Left = 6
        newTxtBox.Height = 15
        newTxtBox.Width = 73.5
        newTxtBox.Text = todosBECs(p)
        
        Set newTxtBox = Me.Controls.Add("Forms.textbox.1", "Base" & p, True)
        
        newTxtBox.Name = "Base" & p
        newTxtBox.Top = 36 + 18 * p
        newTxtBox.Left = 6
        newTxtBox.Height = 15
        newTxtBox.Width = 73.5
        newTxtBox.Text = todasBases(p)
        
        Set newTxtBox = Me.Controls.Add("Forms.TextBox.1", "NomeCliente" & p, True)
                        
        newTxtBox.Name = "NomeCliente" & p
        newTxtBox.Top = 36 + 18 * p
        newTxtBox.Left = 138
        newTxtBox.Height = 15
        newTxtBox.Width = 240
        newTxtBox.Text = todosClientes(p)
        
        Set newTxtBox = Me.Controls.Add("Forms.textbox.1", "valorBEC" & p, True)
        
        newTxtBox.Name = "valorBEC" & p
        newTxtBox.Top = 36 + 18 * p
        newTxtBox.Left = 384
        newTxtBox.Height = 15
        newTxtBox.Width = 108
        newTxtBox.Text = todosValores(p)
    
        Set newChkBox = Me.Controls.Add("forms.checkbox.1", "check" & p, True)
        
        newChkBox.Name = "check" & p
        newChkBox.Top = 39 + 18 * p
        newChkBox.Left = 498
        newChkBox.Height = 18
        newChkBox.Width = 15
        
    Next p
    
    'Then obviously when all is done and all textboxes are added I have to add the OK and
    'cancel button. "Puxar" is the caption for the ok button because if means "to pull"
    'since the user is pulling data from the Swb to his Cwb. "Cancelar" is fairly obvious
    
    Set newOkBut = Me.Controls.Add("Forms.commandbutton.1", "LBOk", True)
    
    newOkBut.Name = "LBOk"
    newOkBut.Top = 36 + 18 * (p + 1)
    newOkBut.Left = 378
    newOkBut.Width = 60
    newOkBut.Caption = "Puxar"
    
    Set newCnlBut = Me.Controls.Add("Forms.commandbutton.1", "LBCancel", True)
    
    newCnlBut = "LBCancel"
    newCnlBut.Top = 36 + 18 * (p + 1)
    newCnlBut.Left = 444
    newCnlBut.Width = 60
    newCnlBut.Caption = "Cancelar"
    
'EH:
    
End Sub

Again, any help is needed. Thanks in advance!