Bon de commande / VBA

Bonjour,

J’ai réussi à faire un fichier « bon de commande ».

Les personnes recherchent les articles par la référence et les ajoutent au panier via le userform.

Ce fichier va permettre de créer les bons de commandes et d’importer celles-ci dans notre ERP sans ressaisie.

Il y a 4 choses qui me restent à faire sur ce fichier et que je n’arrive pas à faire faute de connaissance VBA.

• Il faut que j’ajoute en bas de mon tableau une ligne de total (colonne X). Le problème est que si j’ajoute un article au panier entre temps, ma ligne total est écrasé par la ligne article. Comment faire pour ajouter cette ligne « total » et qu’elle se décale en fonction des ajouts des articles ?

• J’ai fait également un export au format .txt (Fonction qui se trouve dans le module 2) qui permet d’intégrer la commande dans notre ERP. Le seul problème est que j’aurais besoin, lorsque la colonne A (colonne repère) est renseigné, de générer une ligne au-dessus et que le repère s’intègre dans la ligne I et que la lettre « C » s’intègre dans la colonne E et F. Egalement je ne dois pas exporter dans le TXT la ligne « total » qui créerait une erreur lors de l’importation dans l’ERP. J’ai mis un onglet « Export TXT » afin de montrer comment doit être le format.

• Egalement, je dois faire un export XLS à destination des clients. Le fichier doit être le même mais sans les colonnes Y à AD qui mentionnent les prix d’achat et sans les autres onglets. Est-ce possible de faire cela en VBA sachant que j’ai une image dans l’onglet?

• Dans le USERFORM consultation, j’ai besoin de mettre une formule dans textBox48 (PU HT).
  La formule doit être la suivante : TextBox48.Value = (TextBox72.Value * (100 - TextBox72.Value)/100) mais ca ne fonctionne pas en mettant la formule comme cela.

Le fichier excel est disponible ici : http://jeuouest.free.fr/excel/offre-prix-excelpratique.xlsm

Merci pour votre aide.

Bonjour,

Du fait que ton tableau est un tableau Excel = Tableau2, il faut utiliser les actions associées à cet objet pour ajouter une ligne. De plus, il faut profiter dans ton code des propriétés de cet objet. J'ai donc remanié ton code comme suit :

Private Sub valid_modif_Click()
        Dim ligne_ajoutée As Range

        'On ajoute une ligne au tableau avec les données renseigées dans le USEFORM
         Feuil1.ListObjects("Tableau2").Resize [Tableau2].Offset(-1).Resize([Tableau2].Rows.Count + 2 + 1)  ' (+2 = ligne entête + ligne total)
         Set ligne_ajoutée = [Tableau2].Rows([Tableau2].Rows.Count)

        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("U") = Qt
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("W") = PUV
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("T") = descriptif
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("A") = reperage
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("Z") = PA
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("AA") = rem_fr
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("E") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("B") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("C") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("D") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("G") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("P") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("Q") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("R") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("S") = "0"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("E") = "A"
        If choisi <> "" Then ligne_ajoutée.Columns("H") = choisi: ligne_ajoutée.Columns("F") = "D"

End Sub

Merci Thev, ca marche pour le total.

Bien.

Je regarde la partie Export. Pour ton info, l'objet Range [Tableau2] ne comprend ni l'entête, ni le total de ton tableau, mais uniquement ses lignes. C'est donc cet objet qu'il faut utiliser.

ci-dessous code pour exportation

Sub SaveAsTextFile()

    Dim Tb_export As Object
    Dim Ligne  As Variant
    Dim fFilename As String
    Dim a As Integer, b As Integer
    Dim tmP As String
    Dim Separateur, Repère, ligne_export As String
    Dim ligne_modèle()

    'On sépare avec les tabulations pour l'ERP
     Separateur = vbTab

    'assignation de l'application Excel
     Set xl = Application

    'Stockage des lignes de Tableau2 dans le tableau_objet tb_export
     Set Tb_export = CreateObject("System.Collections.ArrayList")
    For Each Ligne In [Tableau2].Columns("B:X").Rows
        If Not IsEmpty(Ligne.Offset(, -1).Columns("A")) Then
            Repère = Ligne.Offset(, -1).Columns("A")
            ligne_modèle = Array(0, 0, 0, "C", "C", 0, "", "" & Repère & "", 0, 0, "", 0, "", "", 0, 0, 0, 0)
            ligne_export = Join(ligne_modèle, Separateur)
            Tb_export.Add (ligne_export)
        End If
        ' transformation données de la ligne en chaîne délimitée par séparateur
         ligne_export = Join(xl.Transpose(xl.Transpose(Ligne.Value)), Separateur)
        Tb_export.Add (ligne_export)
    Next

    'Exportation dans un fichier Texte à partir du tableau_objet Tbexport
     fFilename = Application.GetSaveAsFilename(InitialFileName:="export-ficom", fileFilter:="Text Files (*.txt), *.txt")
    Open fFilename For Output As #1
        For Each Ligne In Tb_export
            Print #1, Ligne
        Next
    Close #1

End Sub

Dans le USERFORM consultation, j’ai besoin de mettre une formule dans textBox48 (PU HT).
La formule doit être la suivante : TextBox48.Value = (TextBox72.Value * (100 - TextBox72.Value)/100) mais ca ne fonctionne pas en mettant la formule comme cela

Si tu insères cette formule comme ci-dessous, ça fonctionne :

Private Sub TextBox72_Change()

   TextBox48.Value = (TextBox72.Value * (100 - TextBox72.Value) / 100)

End Sub

Merci. Ca marche également pour l'export TXT

J'ai fait juste une petite modif pour le format d'export car je mettais trompé mais le code est compréhensible pour moi .

Pour la formule TextBox48.Value = (TextBox72.Value * (100 - TextBox71.Value) / 100), lorsque je l'ai met dans le user form

Private Sub TextBox48_Change()
TextBox48.Value = (TextBox72.Value * (100 - TextBox71.Value) / 100)
If IsNumeric(TextBox48.Value) Then
    PUV = TextBox48
    Else
    MsgBox ("Vous n'avez pas rentré une valeur numérique")
End If
End Sub

Je n'ai pas de message d'erreur mais la formule ne s'éxécute pas en temps réel. Je pense que je me suis mal exprimé. je souhaiterais voir directement le prix unitaire après avoir rentré une remise sur le Prix public dans la texbox.value.71

Bonjour Thev,

Concernant l'enregistrement au format excel, j'ai réussi à faire ce que je souhaitais. Voici le code.

Sub Enr_XLS()
Dim Chemin As String, Fichier As String
Dim NbLg As Long

  Application.ScreenUpdating = False
  Chemin = ThisWorkbook.Path & Application.PathSeparator
  'Je donne le nom du fichier Offre de prix _ nom du chantier_ N° de version
  Fichier = "Offre_de_prix" & "_" & Range("I6").Value & "_" & Range("X1").Value
  'Je copie uniquement l'offre de prix
  Sheets("offre de prix").Copy
  With ActiveWorkbook
    With .Sheets(1)
      NbLg = .Range("A" & Rows.Count).End(xlUp).Row
      'Je copie uniquement les colonnes A1 à X
      .Range("A1:X" & NbLg).Copy
      'Je colle uniquement les valeurs
      .Range("A1").PasteSpecial Paste:=xlPasteValues
      ' Je supprime les colonnes Y à AF
      .Range("Y:AF").Delete
      .Range("A1").Select
    End With
    .SaveAs Chemin & Fichier & ".xlsx"
    .Close
  End With
  MsgBox "Fichier créé"
End Sub

Il ne me reste plus que les formules dans le USERFORM

Dans le USERFORM consultation, j’ai besoin de mettre une formule dans textBox48 (PU HT).

La formule doit être la suivante : TextBox48.Value = (TextBox72.Value * (100 - TextBox72.Value)/100) mais ca ne fonctionne pas en mettant la formule comme cela.

Et la je sèche car je souhaiterais que la formule se calcule directement dans le USERFORM.

Bonjour,

J'ai un peu de mal à comprendre. Quelle est la bonne formule ?

TextBox48.Value = (TextBox72.Value * (100 - TextBox72.Value)/100) ?

TextBox48.Value = (TextBox72.Value * (100 - TextBox71.Value) / 100 ?

Si c'est la deuxième, alors il faut la mettre dans

Private Sub TextBox71_Change()
   TextBox48.Value = (TextBox72.Value * (100 - TextBox71.Value) / 100)
End Sub

cette instruction va déclencher la procédure liée à l'événement :

Private Sub TextBox48_Change()
If IsNumeric(TextBox48.Value) Then
    PUV = TextBox48
    Else
    MsgBox ("Vous n'avez pas rentré une valeur numérique")
End If
End Sub

Merci Thev

Effectivement ca fonctionne bien ! Merci !

Par contre, lorsque je supprime la remise dans la TexBox.71 et que je souhaite la remplacer par une autre remise, cela me crée une erreur (Erreur d'execution 13 - Incompatibilité de type)

Dernière question : l'enregistrement au format excel fonctionne bien par contre, lorsque j'ouvre ce nouveau fichier, j'ai des liaisons et je n'arrive pas à les supprimer. Que dois-je ajouter dans mon export pour supprimer les liaisons sachant que je n'ai copié que les valeurs et non les formules?

J'ai trouvé la solution pour l'erreur 13

Private Sub TextBox71_Change()
If TextBox71.Value = "" Then Exit Sub

   If IsNumeric(TextBox71.Value) Then
   TextBox48.Value = (TextBox72.Value * (100 - TextBox71.Value) / 100)
    Else
    MsgBox ("Vous n'avez pas rentré une valeur numérique")
End If
End Sub

Bonsoir,

Pour la première question, il suffit de rajouter une instruction de ce type dans l'événement Change de textbox71

Private Sub TextBox71_Change()
    If Not IsNumeric(TextBox71.Value) Then TextBox71.Value = 0
    TextBox48.Value = (TextBox72.Value * (100 - TextBox71.Value) / 100)
End Sub

Pour la seconde question, il faut supprimer les noms faisant référence à un classeur externe, comme ceci :

    Names("l_article").Delete
    Names("p_article").Delete
    Names("f_article").Delete

Bonjour,

J'ai 3 dernières questions.

• Lorsque je clique sur le formulaire "Consultation" sur le bouton "Exporter le fichier pour le client", j'ai le message "Voulez vous remplacer le contenu des cellules de destination?" Est ce possible de supprimer cela?

• Egalement, si j'annule l'enregistrement, cela me crée un bug que je n'arrive pas à corriger (Erreur d'execution 1004, la methode pastespecial a echoué). Comme résoudre cela?

• dans le userform "consultation", j'ai fait une formule afin qu'on puisse mettre une remise complémentaire sur le prix d'achat. Si j'applique une remise de 50%, le problème est que la formule s'applique d'abord de 5% car j'ai tapé 5 en 1er chiffre puis de 50%.
  Comment faire pour que la formule ne s'applique pas tout de suite?

Merci

Lionel

Pour la première question, il suffit derrière l'instruction

  Application.ScreenUpdating = False

d'ajouter

Application.DisplayAlerts = False

Merci ca résoud le problème sur les 2 premiers points.

Tes 2 premières questions sont résolues en appliquant cette modification de ton code

Sub Enr_XLS()
Dim Chemin As String, Fichier As String
Dim NbLg As Long

  Application.ScreenUpdating = False
  Chemin = ThisWorkbook.Path & Application.PathSeparator
  'Je donne le nom du fichier Offre de prix _ nom du chantier_ N° de version
   Fichier = "Offre_de_prix" & "_" & Range("I6").Value & "_" & Range("X1").Value
  'Je copie uniquement l'offre de prix
   Sheets("offre de prix").Copy

  With ActiveWorkbook
    With .Sheets(1).UsedRange  'plage utilisée
       .Columns("A:T").Formula = .Columns("A:T").Value    'conservation uniquement des valeurs
       .Columns("Y:AF").EntireColumn.Delete
      .Range("A1").Select
    End With
    .SaveAs Chemin & Fichier & ".xlsx"
    .Close
  End With
  MsgBox "Le Fichier pour le client a été créé"

End Sub

Pour la 3ème question, je changerai l'événement

Private Sub TextBox71_Change()

par

Private Sub TextBox71_AfterUpdate()

Merci Thev. Ca fonctionne.

Y'a t'il un état qui permet le calcul lorsque le curseur sort de la textbox?

j'ai essayé Private Sub TextBox71_Exit() mais ca ne fonctionne pas.

j'ai essayé Private Sub TextBox71_Exit() mais ca ne fonctionne pas

Parce que TextBox71 est inséré dans le contrôle Frame4.

Frame4_Exit() va fonctionner à condition de sortir du cadre : remise et prix unitaire

Bonjour Thev,

On me demande finalement de mettre la ligne "Ligne_modèle" lors de l'export TXT au-dessous et non pas au-dessus.

    Sub SaveAsTextFile()

        Dim Tb_export As Object
        Dim Ligne  As Variant
        Dim fFilename As String
        Dim a As Integer, b As Integer
        Dim tmP As String
        Dim Separateur, Repère, ligne_export As String
        Dim ligne_modèle()

        'On sépare avec les tabulations pour l'ERP
        Separateur = vbTab

        'assignation de l'application Excel
        Set xl = Application

        'Stockage des lignes de Tableau2 dans le tableau_objet tb_export
        Set Tb_export = CreateObject("System.Collections.ArrayList")
        For Each Ligne In [Tableau2].Columns("B:X").Rows
            If Not IsEmpty(Ligne.Offset(, -1).Columns("A")) Then
                Repère = Ligne.Offset(, -1).Columns("A")
                ligne_modèle = Array(0, 0, 0, "C", "C", 0, "", "" & Repère & "", 0, 0, "", 0, "", "", 0, 0, 0, 0)
                ligne_export = Join(ligne_modèle, Separateur)
                Tb_export.Add (ligne_export)
            End If
            ' transformation données de la ligne en chaîne délimitée par séparateur
            ligne_export = Join(xl.Transpose(xl.Transpose(Ligne.Value)), Separateur)
            Tb_export.Add (ligne_export)
        Next

        'Exportation dans un fichier Texte à partir du tableau_objet Tbexport
        fFilename = Application.GetSaveAsFilename(InitialFileName:="export-ficom", fileFilter:="Text Files (*.txt), *.txt")
        Open fFilename For Output As #1
            For Each Ligne In Tb_export
                Print #1, Ligne
            Next
        Close #1

    End Sub

Peux tu m'indiquer ce que je dois modifier pour l'insérer au-dessous?

Merci pour ta grande aide !