Create dynamic textbox by column amount

[Rafael Moraes Santos]

Good evening friends, I'm working on a maintenance control spreadsheet project. I'm having trouble doing the loop that creating the fields as filled in the worksheet creates only the amount of textbox required per line. The second question is in relation to making these fields bring the values only if that machine is matched and the item number is the same.



It follows my code below which is within a module and is called when starting the Jframe


Sub Cabeçario_item()
'Add Dynamic Label and assign it to object 'Lbl'
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "ITEM"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 30

'Label Position
lbl.Left = 0
lbl.Top = 10
End Sub
Sub Carregartag()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Cabeçário As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 100) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
l = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i)
dict.Add Worksheets(1).Cells(i, 9), charArray
Next i

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = 10
Planos.Left = l + 30
Planos.Height = 120
Planos.Width = 30
Planos.Font.Name = "Vertigo Upright 2 BRK"
Planos.Font.Size = 11
Planos.ForeColor = &HFF0000
Planos.Font.Bold = True
Planos.MultiLine = True
Planos.Value = dict.Keys(k - 1)
l = l + 30

Next k


For Cabeçário = 1 To 1
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "CONJUNTO"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 100

'Label Position
lbl.Left = l + 30
lbl.Top = 10

l = l + 100
Next Cabeçário

For Cabeçário = 1 To 1
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "TAREFA"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 100

'Label Position
lbl.Left = l + 30
lbl.Top = 10

l = l + 100
Next Cabeçário

For Cabeçário = 1 To 1
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "SERVIÇO"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 500

'Label Position
lbl.Left = l + 30
lbl.Top = 10

l = l + 100
Next Cabeçário

For Cabeçário = 1 To 1
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "TIPO"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 100

'Label Position
lbl.Left = 730 + (30 * c)
lbl.Top = 10

Next Cabeçário

For Cabeçário = 1 To 1
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "FREQUÊNCIA"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 30

'Label Position
lbl.Left = 830 + (30 * c)
lbl.Top = 10

Next Cabeçário

For Cabeçário = 1 To 1
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "CUSTO UNITÁRIO"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 100

'Label Position
lbl.Left = 860 + (30 * c)
lbl.Top = 10

Next Cabeçário

For Cabeçário = 1 To 1
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
Set lbl = UserForm1.Planos.Controls.Add("Forms.Label.1")

'Assign Label Name
lbl.Caption = "CUSTO TOTAL"
lbl.Enabled = True
lbl.BackColor = &H8000000B
lbl.Font.Name = "Arial"
lbl.Font.Size = 11
lbl.TextAlign = fmTextAlignCenter
lbl.Enabled = False
lbl.BorderStyle = fmBorderStyleSingle
lbl.BorderColor = &H80000000

lbl.Height = 120
lbl.Width = 100

'Label Position
lbl.Left = 960 + (30 * c)
lbl.Top = 10

Next Cabeçário

End Sub
Sub Carregarplanos()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 100) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
l = 0
B = dict.Keys



For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 1)
charArray(2) = Worksheets(1).Cells(i, 2)
dict.Add Worksheets(1).Cells(i, 10), charArray
Next i

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = 130
Planos.Left = l + 30
Planos.Height = 20
Planos.Width = 30
Planos.Font.Name = "Arial"
Planos.Font.Size = 11
Planos.TextAlign = fmTextAlignCenter
Planos.SelectionMargin = False

If dict.Exists(charArray(1)) And dict.Exists(charArray(2)) Then
Planos.Value = dict.Keys(k - 1)
Else
Planos.Value = dict.Keys(k - 1)
End If

h = h + 20
l = l + 30

Next k
End Sub
Sub Carregaritem()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 2)
dict.Add Worksheets(1).Cells(i, 1), charArray
Next i

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 0
Planos.Height = 20
Planos.Width = 30
Planos.Enabled = False
Planos.Font.Name = "Arial"
Planos.Font.Size = 12
Planos.BorderStyle = fmBorderStyleNone
Planos.BorderColor = &H80000010
Planos.SelectionMargin = False
''Planos.Locked = True
''Planos.Font.Bold = True
''Planos.ForeColor = &HFF&
Planos.SpecialEffect = fmSpecialEffectRaised
Planos.TextAlign = fmTextAlignCenter
Planos.BackColor = &H8000000B
Planos.Value = dict.Keys(k - 1)
h = h + 20
Next k

End Sub
Sub Carregarconjuntos()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 3)
dict.Add Worksheets(1).Cells(i, 2), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 30 + (30 * c)
Planos.Height = 20
Planos.Width = 100
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.SelectionMargin = False
Planos.Value = dict.Keys(k - 1)
h = h + 20
Next k

End Sub
Sub Carregartarefas()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 4)
dict.Add Worksheets(1).Cells(i, 3), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 130 + (30 * c)
Planos.Height = 20
Planos.Width = 100
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.SelectionMargin = False
Planos.Value = dict.Keys(k - 1)
h = h + 20
Next k

End Sub
Sub Carregarserviços()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 5)
dict.Add Worksheets(1).Cells(i, 4), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 230 + (30 * c)
Planos.Height = 20
Planos.Width = 500
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.SelectionMargin = False
Planos.Value = dict.Keys(k - 1)
h = h + 20
Next k

End Sub
Sub Carregartipo()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.ComboBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 6)
dict.Add Worksheets(1).Cells(i, 5), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.ComboBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 730 + (30 * c)
Planos.Height = 20
Planos.Width = 100
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.AddItem "AUTOMAÇÃO"
Planos.AddItem "ELÉTRICO"
Planos.AddItem "LUBRIFICAÇÃO"
Planos.AddItem "MATRIZARIA"
Planos.AddItem "MECÂNICO"
Planos.Value = dict.Keys(k - 1)
h = h + 20
Next k

End Sub
Sub Carregarfrequência()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 7)
dict.Add Worksheets(1).Cells(i, 6), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 830 + (30 * c)
Planos.Height = 20
Planos.Width = 30
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.TextAlign = fmTextAlignCenter
Planos.SelectionMargin = False
Planos.Value = dict.Keys(k - 1)
h = h + 20
Next k

End Sub
Sub Carregarcustounitario()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 8)
dict.Add Worksheets(1).Cells(i, 7), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 860 + (30 * c)
Planos.Height = 20
Planos.Width = 100
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.TextAlign = fmTextAlignCenter
Planos.SelectionMargin = False
Planos.Value = dict.Keys(k - 1)
Planos.Value = Format(Planos, "R$ ##,##0.00")
h = h + 20
Next k

End Sub
Sub Carregarcustototal()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim dict As New Scripting.Dictionary
Dim charArray(1 To 1) As String
Dim ColHeader As String
Dim Planos As MSForms.TextBox
Dim h As Integer

j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
h = 0
B = dict.Keys

For i = 1 To j
charArray(1) = Worksheets(1).Cells(i, 9)
dict.Add Worksheets(1).Cells(i, 8), charArray
Next i

Dim c As Integer
c = WorksheetFunction.CountA(Sheets(1).Range("A:A"))

For k = 1 To j
Set Planos = UserForm1.Planos.Controls.Add("Forms.TextBox.1", "Planos")
Planos.Top = h + 130
Planos.Left = 960 + (30 * c)
Planos.Height = 20
Planos.Width = 100
Planos.Font.Name = "Arial"
Planos.Font.Size = 10
Planos.TextAlign = fmTextAlignCenter
Planos.SelectionMargin = False
Planos.Value = dict.Keys(k - 1)
Planos.Value = Format(Planos, "R$ ##,##0.00")
h = h + 20
Next k

End Sub




This module is called in the jframe via the code below:


Private Sub UserForm_Initialize()

Call Carregaritem
Call Carregarserviços
Call Carregarconjuntos
Call Carregartarefas
Call Carregartipo
Call Carregarfrequência
Call Carregarcustounitario
Call Carregarcustototal
Call Carregartag
Call Carregarplanos
Call Cabeçario_item

End Sub