Remplissage a condition depuis une form VBA

Mizo1983 Messages postés 5 Statut Membre -  
Mizo1983 Messages postés 5 Statut Membre -
Bonjour,
Merci tout d’abord pour tous ceux qui vont prendre le temps a lire ma question et me répondre:

j'ai créer une forme qui peux enregistrer des données depuis des combox et textbox sur VB



j’ai mis 10 produits a remplir


le problème qu j’ai vécu si l’utilisateur veux enregistrer seulement 1 ou moins de 10 produits



qui peut m'aider a arrêter la boucle au dernier produit rempli le code est comme suit: 

Private Sub CommandButton_Save_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Daily Movment")


    If Me.ComboBox_Product_1.Value = "" Then
       MsgBox "Please add a product into product box"
       Exit Sub
    End If

    If IsNumeric(Me.TextBox_QTY_1.Value) = False Then
       MsgBox "Invalid Qty"
       Exit Sub
    End If
    
    If Me.TextBox_UNIT_1.Value = "" Then
       MsgBox "Please add the Unit into Unit box"
       Exit Sub
    End If
    

Set sh = ThisWorkbook.Sheets("Daily Movment")


 Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

sh.Range("A" & lr + 1).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 1).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 1).Value = Me.ComboBox_Product_1.Value
sh.Range("D" & lr + 1).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 1).Value = Me.TextBox_QTY_1.Value
sh.Range("F" & lr + 1).Value = Me.TextBox_UNIT_1.Value
sh.Range("G" & lr + 1).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 1).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 1).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 1).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 1).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 1).Value = Me.ComboBox_Request.Value


sh.Range("A" & lr + 2).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 2).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 2).Value = Me.ComboBox_Product_2.Value
sh.Range("D" & lr + 2).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 2).Value = Me.TextBox_QTY_2.Value
sh.Range("F" & lr + 2).Value = Me.TextBox_UNIT_2.Value
sh.Range("G" & lr + 2).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 2).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 2).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 2).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 2).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 2).Value = Me.ComboBox_Request.Value

sh.Range("A" & lr + 3).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 3).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 3).Value = Me.ComboBox_Product_3.Value
sh.Range("D" & lr + 3).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 3).Value = Me.TextBox_QTY_3.Value
sh.Range("F" & lr + 3).Value = Me.TextBox_UNIT_3.Value
sh.Range("G" & lr + 3).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 3).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 3).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 3).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 3).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 3).Value = Me.ComboBox_Request.Value
  
sh.Range("A" & lr + 4).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 4).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 4).Value = Me.ComboBox_Product_4.Value
sh.Range("D" & lr + 4).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 4).Value = Me.TextBox_QTY_4.Value
sh.Range("F" & lr + 4).Value = Me.TextBox_UNIT_4.Value
sh.Range("G" & lr + 4).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 4).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 4).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 4).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 4).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 4).Value = Me.ComboBox_Request.Value

sh.Range("A" & lr + 5).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 5).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 5).Value = Me.ComboBox_Product_5.Value
sh.Range("D" & lr + 5).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 5).Value = Me.TextBox_QTY_5.Value
sh.Range("F" & lr + 5).Value = Me.TextBox_UNIT_5.Value
sh.Range("G" & lr + 5).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 5).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 5).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 5).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 5).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 5).Value = Me.ComboBox_Request.Value

  
sh.Range("A" & lr + 6).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 6).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 6).Value = Me.ComboBox_Product_6.Value
sh.Range("D" & lr + 6).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 6).Value = Me.TextBox_QTY_6.Value
sh.Range("F" & lr + 6).Value = Me.TextBox_UNIT_6.Value
sh.Range("G" & lr + 6).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 6).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 6).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 6).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 6).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 6).Value = Me.ComboBox_Request.Value
  
sh.Range("A" & lr + 7).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 7).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 7).Value = Me.ComboBox_Product_7.Value
sh.Range("D" & lr + 7).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 7).Value = Me.TextBox_QTY_7.Value
sh.Range("F" & lr + 7).Value = Me.TextBox_UNIT_7.Value
sh.Range("G" & lr + 7).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 7).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 7).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 7).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 7).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 7).Value = Me.ComboBox_Request.Value


  
sh.Range("A" & lr + 8).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 8).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 8).Value = Me.ComboBox_Product_8.Value
sh.Range("D" & lr + 8).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 8).Value = Me.TextBox_QTY_8.Value
sh.Range("F" & lr + 8).Value = Me.TextBox_UNIT_8.Value
sh.Range("G" & lr + 8).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 8).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 8).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 8).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 8).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 8).Value = Me.ComboBox_Request.Value

  
sh.Range("A" & lr + 9).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 9).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 9).Value = Me.ComboBox_Product_9.Value
sh.Range("D" & lr + 9).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 9).Value = Me.TextBox_QTY_9.Value
sh.Range("F" & lr + 9).Value = Me.TextBox_UNIT_9.Value
sh.Range("G" & lr + 9).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 9).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 9).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 9).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 9).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 9).Value = Me.ComboBox_Request.Value

  
sh.Range("A" & lr + 10).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 10).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 10).Value = Me.ComboBox_Product_10.Value
sh.Range("D" & lr + 10).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 10).Value = Me.TextBox_QTY_10.Value
sh.Range("F" & lr + 10).Value = Me.TextBox_UNIT_10.Value
sh.Range("G" & lr + 10).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 10).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 10).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 10).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 10).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 10).Value = Me.ComboBox_Request.Value
    


sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub


End Sub



Configuration: Configuration: Configuration: Windows / Chrome 102.0.5005.63

5 réponses

Réponse 1 / 5
yg_be Messages postés 24281 Date d'inscription   Statut Contributeur Dernière intervention   Ambassadeur 1 585
 
bonjour,
peut-être en utilisant un if pour tester si le produit est vide?
0
Réponse 2 / 5
Mizo1983 Messages postés 5 Statut Membre
 
J'ai essayer ca a marcher seulement avec les 2 premier ligne
0
yg_be Messages postés 24281 Date d'inscription   Statut Contributeur Dernière intervention   1 585
 
montre ce que tu as essayé pour les autres lignes.
0
Réponse 3 / 5
Mizo1983 Messages postés 5 Statut Membre
 
Si J'ajoute autre else la boucle ne marche plus et rien ne s'enregistre: 


Private Sub CommandButton_Save_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Daily Movment")


    If Me.ComboBox_Product_1.Value = "" Then
       MsgBox "Please add a product into product box"
       Exit Sub
    End If

    If IsNumeric(Me.TextBox_QTY_1.Value) = False Then
       MsgBox "Invalid Qty"
       Exit Sub
    End If
    
    If Me.TextBox_UNIT_1.Value = "" Then
       MsgBox "Please add the Unit into Unit box"
       Exit Sub
    End If
    

Set sh = ThisWorkbook.Sheets("Daily Movment")


 Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

sh.Range("A" & lr + 1).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 1).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 1).Value = Me.ComboBox_Product_1.Value
sh.Range("D" & lr + 1).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 1).Value = Me.TextBox_QTY_1.Value
sh.Range("F" & lr + 1).Value = Me.TextBox_UNIT_1.Value
sh.Range("G" & lr + 1).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 1).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 1).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 1).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 1).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 1).Value = Me.ComboBox_Request.Value

If Me.ComboBox_Product_2.Value = "" Then
       sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
       Exit Sub
   Else

sh.Range("A" & lr + 2).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 2).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 2).Value = Me.ComboBox_Product_2.Value
sh.Range("D" & lr + 2).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 2).Value = Me.TextBox_QTY_2.Value
sh.Range("F" & lr + 2).Value = Me.TextBox_UNIT_2.Value
sh.Range("G" & lr + 2).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 2).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 2).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 2).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 2).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 2).Value = Me.ComboBox_Request.Value

sh.Range("A" & lr + 3).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 3).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 3).Value = Me.ComboBox_Product_3.Value
sh.Range("D" & lr + 3).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 3).Value = Me.TextBox_QTY_3.Value
sh.Range("F" & lr + 3).Value = Me.TextBox_UNIT_3.Value
sh.Range("G" & lr + 3).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 3).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 3).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 3).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 3).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 3).Value = Me.ComboBox_Request.Value
  
sh.Range("A" & lr + 4).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 4).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 4).Value = Me.ComboBox_Product_4.Value
sh.Range("D" & lr + 4).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 4).Value = Me.TextBox_QTY_4.Value
sh.Range("F" & lr + 4).Value = Me.TextBox_UNIT_4.Value
sh.Range("G" & lr + 4).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 4).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 4).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 4).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 4).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 4).Value = Me.ComboBox_Request.Value

sh.Range("A" & lr + 5).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 5).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 5).Value = Me.ComboBox_Product_5.Value
sh.Range("D" & lr + 5).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 5).Value = Me.TextBox_QTY_5.Value
sh.Range("F" & lr + 5).Value = Me.TextBox_UNIT_5.Value
sh.Range("G" & lr + 5).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 5).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 5).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 5).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 5).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 5).Value = Me.ComboBox_Request.Value

  
sh.Range("A" & lr + 6).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 6).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 6).Value = Me.ComboBox_Product_6.Value
sh.Range("D" & lr + 6).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 6).Value = Me.TextBox_QTY_6.Value
sh.Range("F" & lr + 6).Value = Me.TextBox_UNIT_6.Value
sh.Range("G" & lr + 6).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 6).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 6).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 6).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 6).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 6).Value = Me.ComboBox_Request.Value
  
sh.Range("A" & lr + 7).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 7).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 7).Value = Me.ComboBox_Product_7.Value
sh.Range("D" & lr + 7).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 7).Value = Me.TextBox_QTY_7.Value
sh.Range("F" & lr + 7).Value = Me.TextBox_UNIT_7.Value
sh.Range("G" & lr + 7).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 7).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 7).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 7).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 7).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 7).Value = Me.ComboBox_Request.Value


  
sh.Range("A" & lr + 8).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 8).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 8).Value = Me.ComboBox_Product_8.Value
sh.Range("D" & lr + 8).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 8).Value = Me.TextBox_QTY_8.Value
sh.Range("F" & lr + 8).Value = Me.TextBox_UNIT_8.Value
sh.Range("G" & lr + 8).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 8).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 8).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 8).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 8).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 8).Value = Me.ComboBox_Request.Value

  
sh.Range("A" & lr + 9).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 9).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 9).Value = Me.ComboBox_Product_9.Value
sh.Range("D" & lr + 9).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 9).Value = Me.TextBox_QTY_9.Value
sh.Range("F" & lr + 9).Value = Me.TextBox_UNIT_9.Value
sh.Range("G" & lr + 9).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 9).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 9).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 9).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 9).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 9).Value = Me.ComboBox_Request.Value

  
sh.Range("A" & lr + 10).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 10).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 10).Value = Me.ComboBox_Product_10.Value
sh.Range("D" & lr + 10).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 10).Value = Me.TextBox_QTY_10.Value
sh.Range("F" & lr + 10).Value = Me.TextBox_UNIT_10.Value
sh.Range("G" & lr + 10).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 10).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 10).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 10).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 10).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 10).Value = Me.ComboBox_Request.Value
    




sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
 End If


End Sub
0
yg_be Messages postés 24281 Date d'inscription   Statut Contributeur Dernière intervention   1 585
 
Merci d'utiliser les balises de code quand tu partages du code: https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code

Le code que tu as partagé ne fonctionne pas?
0
Réponse 4 / 5
Mizo1983 Messages postés 5 Statut Membre
 
Si ca Marche Mais si j'ajoute une autre " Else " ca ne marche plus!
0
yg_be Messages postés 24281 Date d'inscription   Statut Contributeur Dernière intervention   1 585
 
Montre nous alors comment tu ajoutes l'autre 
else
, et explique-nous "ca ne marche plus".
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Réponse 5 / 5
Mizo1983 Messages postés 5 Statut Membre
 
thank you "yg_be"

j'ai resolu le probleme ca etait une mauvaise manipulation de else if:

Private Sub CommandButton_Save_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Daily Movment")



If Me.ComboBox_Product_1.Value = "" Then
MsgBox "Please add a product into product box"
Exit Sub
End If

If IsNumeric(Me.TextBox_QTY_1.Value) = False Then
MsgBox "Invalid Qty"
Exit Sub
End If

If Me.TextBox_UNIT_1.Value = "" Then
MsgBox "Please add the Unit into Unit box"
Exit Sub
End If


Set sh = ThisWorkbook.Sheets("Daily Movment")


Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

sh.Range("A" & lr + 1).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 1).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 1).Value = Me.ComboBox_Product_1.Value
sh.Range("D" & lr + 1).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 1).Value = Me.TextBox_QTY_1.Value
sh.Range("F" & lr + 1).Value = Me.TextBox_UNIT_1.Value
sh.Range("G" & lr + 1).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 1).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 1).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 1).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 1).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 1).Value = Me.ComboBox_Request.Value

If Me.ComboBox_Product_2.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else

sh.Range("A" & lr + 2).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 2).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 2).Value = Me.ComboBox_Product_2.Value
sh.Range("D" & lr + 2).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 2).Value = Me.TextBox_QTY_2.Value
sh.Range("F" & lr + 2).Value = Me.TextBox_UNIT_2.Value
sh.Range("G" & lr + 2).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 2).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 2).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 2).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 2).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 2).Value = Me.ComboBox_Request.Value

If Me.ComboBox_Product_3.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else

sh.Range("A" & lr + 3).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 3).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 3).Value = Me.ComboBox_Product_3.Value
sh.Range("D" & lr + 3).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 3).Value = Me.TextBox_QTY_3.Value
sh.Range("F" & lr + 3).Value = Me.TextBox_UNIT_3.Value
sh.Range("G" & lr + 3).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 3).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 3).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 3).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 3).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 3).Value = Me.ComboBox_Request.Value


If Me.ComboBox_Product_4.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 4).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 4).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 4).Value = Me.ComboBox_Product_4.Value
sh.Range("D" & lr + 4).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 4).Value = Me.TextBox_QTY_4.Value
sh.Range("F" & lr + 4).Value = Me.TextBox_UNIT_4.Value
sh.Range("G" & lr + 4).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 4).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 4).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 4).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 4).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 4).Value = Me.ComboBox_Request.Value


If Me.ComboBox_Product_5.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 5).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 5).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 5).Value = Me.ComboBox_Product_5.Value
sh.Range("D" & lr + 5).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 5).Value = Me.TextBox_QTY_5.Value
sh.Range("F" & lr + 5).Value = Me.TextBox_UNIT_5.Value
sh.Range("G" & lr + 5).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 5).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 5).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 5).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 5).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 5).Value = Me.ComboBox_Request.Value


If Me.ComboBox_Product_6.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 6).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 6).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 6).Value = Me.ComboBox_Product_6.Value
sh.Range("D" & lr + 6).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 6).Value = Me.TextBox_QTY_6.Value
sh.Range("F" & lr + 6).Value = Me.TextBox_UNIT_6.Value
sh.Range("G" & lr + 6).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 6).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 6).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 6).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 6).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 6).Value = Me.ComboBox_Request.Value



If Me.ComboBox_Product_7.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 7).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 7).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 7).Value = Me.ComboBox_Product_7.Value
sh.Range("D" & lr + 7).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 7).Value = Me.TextBox_QTY_7.Value
sh.Range("F" & lr + 7).Value = Me.TextBox_UNIT_7.Value
sh.Range("G" & lr + 7).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 7).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 7).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 7).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 7).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 7).Value = Me.ComboBox_Request.Value


If Me.ComboBox_Product_8.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 8).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 8).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 8).Value = Me.ComboBox_Product_8.Value
sh.Range("D" & lr + 8).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 8).Value = Me.TextBox_QTY_8.Value
sh.Range("F" & lr + 8).Value = Me.TextBox_UNIT_8.Value
sh.Range("G" & lr + 8).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 8).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 8).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 8).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 8).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 8).Value = Me.ComboBox_Request.Value

If Me.ComboBox_Product_9.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 9).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 9).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 9).Value = Me.ComboBox_Product_9.Value
sh.Range("D" & lr + 9).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 9).Value = Me.TextBox_QTY_9.Value
sh.Range("F" & lr + 9).Value = Me.TextBox_UNIT_9.Value
sh.Range("G" & lr + 9).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 9).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 9).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 9).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 9).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 9).Value = Me.ComboBox_Request.Value

If Me.ComboBox_Product_10.Value = "" Then
sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
Else
sh.Range("A" & lr + 10).Value = Me.TextBox_Invoice.Value
sh.Range("B" & lr + 10).Value = Me.TextBox_Date.Value
sh.Range("C" & lr + 10).Value = Me.ComboBox_Product_10.Value
sh.Range("D" & lr + 10).Value = Me.ComboBox_Ops.Value
sh.Range("E" & lr + 10).Value = Me.TextBox_QTY_10.Value
sh.Range("F" & lr + 10).Value = Me.TextBox_UNIT_10.Value
sh.Range("G" & lr + 10).Value = Me.ComboBox_Project.Value
sh.Range("H" & lr + 10).Value = Me.ComboBox_received.Value
sh.Range("I" & lr + 10).Value = Me.ComboBox_Sent.Value
sh.Range("J" & lr + 10).Value = Me.ComboBox_driver.Value
sh.Range("K" & lr + 10).Value = Me.ComboBox_Approve.Value
sh.Range("L" & lr + 10).Value = Me.ComboBox_Request.Value
End If

End If

End If

End If

End If


End If

End If

End If





sh.Range("M1:N" & lr).FillDown
MsgBox "Updated!!!"
Exit Sub
End If


End Sub
0

Discussions similaires

Comment donner accès à un Google Form sans compte Gmail
haffinger -
beatrice -

7 réponses
"L'indice n'appartient à la sélection" + VBA
fleur.31 -
geosevda -

2 réponses
google form adresse mail du créateur non visible
Edalice -
Edalice -

2 réponses
remplissage automatique d'une case si une conditions est remplie
secutor08 -
via55 -

4 réponses
Erreur d'exécution 13 Incompatibilité de type
grace -
pijaku -

5 réponses