Remplissage a condition depuis une form VBA

Mizo1983 Messages postés 5 Date d'inscription Statut Membre Dernière intervention -
Mizo1983 Messages postés 5 Date d'inscription Statut Membre Dernière intervention - 13 juin 2022 à 11:46

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

A voir également:

Remplissage a condition depuis une form VBA
Excel cellule couleur si condition texte - Guide
Excel colorer une cellule sous condition d'une autre cellule ✓ - Forum Excel
Un agent immobilier a préparé un diaporama pour présenter une maison à vendre. appliquez la mise en forme de la première diapositive à toutes les autres. quel est le code du portail ? ✓ - Forum Powerpoint
Raccourci remplissage couleur excel ✓ - Forum Bureautique
Excel condition ou - Guide

5 réponses

Réponse 1 / 5

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention Ambassadeur 1 584

bonjour,
peut-être en utilisant un if pour tester si le produit est vide?

Réponse 2 / 5

Mizo1983 Messages postés 5 Date d'inscription Statut Membre Dernière intervention

J'ai essayer ca a marcher seulement avec les 2 premier ligne

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

montre ce que tu as essayé pour les autres lignes.

Réponse 3 / 5

Mizo1983 Messages postés 5 Date d'inscription Statut Membre Dernière intervention

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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

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?

Réponse 4 / 5

Mizo1983 Messages postés 5 Date d'inscription Statut Membre Dernière intervention

Si ca Marche Mais si j'ajoute une autre " Else " ca ne marche plus!

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584

Montre nous alors comment tu ajoutes l'autre

else

, et explique-nous "ca ne marche plus".

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

Posez votre question

Réponse 5 / 5

Mizo1983 Messages postés 5 Date d'inscription Statut Membre Dernière intervention

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

Discussions similaires

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

"L'indice n'appartient à la sélection" + VBA

google form adresse mail du créateur non visible

Erreur d'exécution 13 Incompatibilité de type

remplissage automatique d'une case si une conditions est remplie

Votre réponse

Discussions similaires