Remplissage a condition depuis une form VBA

Mizo1983 -  
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

  1.  yg_be Contributeur
    bonjour,
    peut-être en utilisant un if pour tester si le produit est vide?
    0
    1. Mizo1983 Auteur
      J'ai essayer ca a marcher seulement avec les 2 premier ligne
      0
      1. yg_be Contributeur
        montre ce que tu as essayé pour les autres lignes.
        0
    2. Mizo1983 Auteur
      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
    3. Mizo1983 Auteur
      Si ca Marche Mais si j'ajoute une autre " Else " ca ne marche plus!
      0
      1. yg_be Contributeur
        Montre nous alors comment tu ajoutes l'autre 
        else
        , et explique-nous "ca ne marche plus".
        0
    5. Mizo1983 Auteur
      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

      Téléphone en condition optimale...?
      Elka_3079

      2 réponses
      Changer la couleur d'une cellule selon son texte
      fantomex

      5 réponses
      erreur d'execution 9, l'indice n'appartient pas à la sélection
      isidoh

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

      2 réponses
      Boucle sur la première diapositive d'une présentation
      ND83

      3 réponses