How to make a loop to link a textbox content to a specific excel sheet with Excel VBA

Question

i created a userform for tracking the consumption of a set of products in a warehouse, where the content of each text box will be assigned to a different excel sheet (saving the consumption history) i would like to ask if it's possible to make a loop for assigning the content of each textbox to a specific sheet instead of repeating the code several times.
i will be really grateful for your help Thank you

 Private Sub CommandButton1_Click()

Dim consobav, consobls, consochar As Worksheet
Dim addnewbav, addnewbls, addnewchar As Range
Dim nombrebavettes, nombreblouses, qttbavettes, qttblouses As Integer


Set consobav = Sheet1
Set consobls = Sheet2
Set consochar = Sheet3
'introduire le nombre introduit dans la text box dans le sheet excel
If nbrbavette.Value = "" Then
qttbavettes = 0
Else
nombrebavettes = CInt(ThisWorkbook.Sheets("sheet1").Range("H2").Value)
qttbavettes = CInt(nbrbavette.Value)
End If
If nombrebavettes < qttbavettes Then
MsgBox "qtt insuffisante: " & ThisWorkbook.Sheets("sheet1").Range("A1").Value
Else
Set addnewbav = consobav.Range("A65356").End(xlUp).Offset(1, 0)
addnewbav.Offset(0, 0).Value = qttbavettes
addnewbav.Offset(0, 1).Value = Time & " " & Date
addnewbav.Offset(0, 1).NumberFormat = "d/m/yyyy"
End If

If nbrbls.Value = "" Then
qttblouses = 0
Else
nombreblouses = CInt(ThisWorkbook.Sheets("sheet2").Range("H2").Value)
qttblouses = CInt(nbrbls.Value)
End If
If nombreblouses < qttblouses Then

MsgBox "qtt insuffisante : " & ThisWorkbook.Sheets("sheet2").Range("A1").Value
Else
Set addnewbls = consobls.Range("A65356").End(xlUp).Offset(1, 0)
addnewbls.Offset(0, 0).Value = qttblouses
addnewbls.Offset(0, 1).Value = Time & " " & Date
addnewbls.Offset(0, 1).NumberFormat = "d/m/yyyy"
End If


Set addnewchar = consochar.Range("A65356").End(xlUp).Offset(1, 0)
addnewchar.Offset(0, 0).Value = TextBox1.Value
addnewchar.Offset(0, 1).Value = Time & " " & Date
addnewchar.Offset(0, 1).NumberFormat = "d/m/yyyy"

Call display
Call Somme_consommation_globale
Call seuil_commande
Call display
Call resetform
Call saving_PDF

End Sub

Answer 1

I think the next code can be the solution for your problem. Please let me note that your statement: Dim consobav, consobls, consochar As Worksheet shows a frequent mistake: consobav and consobls are type variant and only consochar is worksheet. Correctly Dim consobav As Worksheet, consobls As Worksheet, consochar As Worksheet The same is in the next two lines.

Option Base 1
Private Sub mySub()
    Dim tbAllBoxes() As Variant
    'Put all you textboxes into an array
    tbAllBoxes = Array(ManyText.Controls("Textbox1"), ManyText.Controls("Textbox2"), ManyText.Controls("Textbox3"), ManyText.Controls("Textbox4"))

    Dim shAllSheets As Variant
    'Put all your worksheets into an array
    shAllSheets = Array(Worksheets("1"), Worksheets("2"), Worksheets("3"), Worksheets("4"))
    Dim i As Long
    'Use the pair of textboxes and worksheets
    For i = 1 To UBound(tbAllBoxes)
        ' Example: write the content of textboxes in the sheets in order Textbox1 to worksheet("1")
        shAllSheets(i).Range("A2") = tbAllBoxes(i).Text
        'do whatever you would like
    Next i
End Sub