Sujet Précédent Sujet Suivant

Remplissage a condition depuis une form VBA

Fermé
Mizo1983 Messages postés 5 Date d'inscription lundi 13 juin 2022 Statut Membre Dernière intervention 13 juin 2022 - Modifié le 13 juin 2022 à 08:47
Mizo1983 Messages postés 5 Date d'inscription lundi 13 juin 2022 Statut Membre Dernière intervention 13 juin 2022 - 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

5 réponses

Réponse 1 / 5
yg_be Messages postés 23236 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 2 octobre 2024 Ambassadeur 1 539
13 juin 2022 à 08:52
bonjour,
peut-être en utilisant un if pour tester si le produit est vide?
0
Réponse 2 / 5
Mizo1983 Messages postés 5 Date d'inscription lundi 13 juin 2022 Statut Membre Dernière intervention 13 juin 2022
13 juin 2022 à 08:57
J'ai essayer ca a marcher seulement avec les 2 premier ligne
0
yg_be Messages postés 23236 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 2 octobre 2024 1 539
13 juin 2022 à 09:00
montre ce que tu as essayé pour les autres lignes.
0
Réponse 3 / 5
Mizo1983 Messages postés 5 Date d'inscription lundi 13 juin 2022 Statut Membre Dernière intervention 13 juin 2022
Modifié le 13 juin 2022 à 12:00
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 23236 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 2 octobre 2024 1 539
13 juin 2022 à 09:21
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 Date d'inscription lundi 13 juin 2022 Statut Membre Dernière intervention 13 juin 2022
13 juin 2022 à 10:15
Si ca Marche Mais si j'ajoute une autre " Else " ca ne marche plus!
0
yg_be Messages postés 23236 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 2 octobre 2024 1 539
13 juin 2022 à 10:18
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 Date d'inscription lundi 13 juin 2022 Statut Membre Dernière intervention 13 juin 2022
13 juin 2022 à 11:46
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

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

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

3 réponses
Comment donner accès à un Google Form sans compte Gmail
haffinger -
beatrice -

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

5 réponses
Méthode find dans VBA - Recherche de données sous Excel
baissaoui -
baissaoui -

0 réponse