VBA Mettre à jour ma feuille Devis

[Arpette]

Bonjour à toutes et à tous,
celà fait plusieurs jours que je cherche le moyen de mettre à jour ma feuille "Devis" si je change une valeur dans ma feuille "Fournisseur". Exemple dans ma feuille "Fournisseurs" je change le prix de l'heure, toutes les valeurs de cette feuille sont changées. Ce que je souhaiterais, c'est que si mon devis est tapé que ça le modifie. Aujourd'hui je dois tout retaper pour prendre les modification.
Merci de votre aide
@+
Mon Fichier

 

[Gael]

Re : VBA Mettre à jour ma feuille Devis

Bonsoir Arpette, le forum,

Si tu veux avoir un changement automatique, il faut mettre des formules et non pas des valeurs en colonnes D pour le prix et en colonne F pour le résultat.

Dans ton code, juste avant l'inputbox "Saisir quantité" tu remplaces:

Target.Offset(0, 4).Value = r.Offset(0, 4).Value

par:

Target.Offset(0, 4).Formula = "=Fournisseurs!$E$" & r.Row

De cette façon, tu mets dans la cellule du Devis un lien avec le prix de la feuille fournisseur.

Pour la colonne F, montant, il faut multiplier D et E et donc remplacer:

Cells(Target.Row, 6).Value = Application.WorksheetFunction.Round _
    (IIf(Cells(Target.Row, 4).Value * Cells(Target.Row, 5).Value = 0, "", Cells(Target.Row, 4).Value * Cells(Target.Row, 5).Value), 2)

par:

Cells(Target.Row, 6).Formula = "=$D" & Target.Row & "*$E" & Target.Row

Si tu veux gérer le montant à 0, il suffit de mettre un format de nombre qui ne mette rien si 0.

Attention: cela ne fonctionnera correctement que s'il n'y a pas d'ajout ou de suppression de lignes dans la feuille fournisseurs, sinon les prix seront décalés.

Il est cependant possible de gérer ce cas en mettant une fonction de recherche dans la formule au lieu d'une simple liaison.

@+

Gael

 

[Gael]

Re : VBA Mettre à jour ma feuille Devis

Re,

Et pour avoir utilisé la liste des produits pendant mes essais, je trouve que ce serait beaucoup plus rapide de faire un doubleclic sur la ligne souhaitée que de la sélectionner puis cliquer sur valider.

Il suffit de remplacer la ligne

Private Sub Valider_Click()

par:

Private Sub LMesfournisseurs_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Gael

 

[Arpette]

Re : VBA Mettre à jour ma feuille Devis

Bonsoir Gaël et merci pour tes deux réponses, elles fonctionnent parfaitement. Par contre je ne souhaiterais que les formules n'apparaissent pas dans la feuille "Devis". J'ai donc rajouté cette ligne:

Target.Offset(0, 4).Formula = "=Fournisseurs!$E$" & r.Row
Target.Offset(0, 4).Value = Target.Offset(0, 4).Value

mais çà ne fonctionne pas puisque je n'ai plus la formule.
Je pense qu'il faudrait qu'à chaque fois qu'il y a une action sur la feuille "Fournisseurs", regarder dans la colonne A se la feuille "Devis" et simuler la saisie des valeurs qui se trouve dans cette colonne. Facile à dire mais...😕
Merci de ton aide

 

[Pierrot93]

Re : VBA Mettre à jour ma feuille Devis

Bonjour,

mais çà ne fonctionne pas puisque je n'ai plus la formule.

pas tout suivi, mais enlève peut être cette ligne :

Target.Offset(0, 4).Value = Target.Offset(0, 4).Value

tu conserveras la formule....

bonne journée
@+

 

[Gael]

Re : VBA Mettre à jour ma feuille Devis

Bonjour Arpette, le forum et salut Pierrot,

On peut effectivement revenir à des constantes dans la feuille devis et ajouter un code pour modifier le devis en cas de changement dans la feuille fournisseur. avant ça, j'ai quelques questions:
* Y-a-t-il souvent des actions sur la feuille Fournisseur, changement de prix, ajout ou suppression de références?
* Quelle est la durée de vie d'un Devis, une fois créé, est-il imprimé puis effaçé ou reste-t-il en attente? combien de temps?
* Pourquoi ne souhaites-tu pas qu'il y ait des formules dans la feuille devis?

@+

Gael

 

[Arpette]

Re : VBA Mettre à jour ma feuille Devis

Bonsoir Gael, Pierrot,
Gael pour répondre à tes questions,
il y a des changements quand une fois le devis fait. On joue sur les prix pour satisfaire le client et l'entrepreneur à savoir on peut être amené à changer les coef si on déclare ou non le déplacement ou la main d'oeuvre...
une fois le devis correct il est archivé et classé
pour les formules c'est trop lourd.

Mais j'ai travaillé un peu, j'ai mis un code dans ma feuille "Fournisseurs" , le problème c'est que je n'arrive pas à faire une action sur la colonne à de ma feuille "Devis" (Activate ne fonctionne pas). Par contre je me suis aperçu que si manuellement je fais un copier collage spécial valeur, la macro devis réagit. Mais impossible de la mettre en VB
Merci de ton aide
@+

 For I = 3 To Range("D65536").End(xlUp).Row
    Cells(I, 5).Value = Application.WorksheetFunction.Round _
    (Application.WorksheetFunction.Sum _
    (Cells(I, 4).Value * Range("G2") + Cells(I, 6).Value * Range("H2")), 2)
    Next
  With Sheets("Devis")
  Dim J
    For J = 20 To .Range("A108").End(xlUp).Row
     .Cells(J, 1).Activate
    Next
  End With

 

[Gael]

Re : VBA Mettre à jour ma feuille Devis

Bonsoir Arpette, le forum,

Je te propose d'ajouter dans la procédure de mise à jour de la feuille fournisseur, une mise à jour du devis en fin de procédure (entre le Endif et Enableevents=true) qui va reprendre le devis ligne à ligne et mettre à jour le libellé, le Prix et le montant (sans formule) puis recalculer le total et la TVA.

Module à insérer (testé et qui marche):

'*** Mise à jour du devis
  Dim J As Integer, cel As Range, r As Range, pl As Range
  
  With Sheets("Fournisseurs")                                   ' Prend en compte l'onglet "Fournisseurs"
  Set pl = .Range("A3:A" & .Range("A65536").End(xlUp).Row)    ' Définit la plage de recherche
  End With                                                      ' Fin de la prise en compte de l'onglet "Fournisseurs"
        
  '*** Mise à jour de chaque ligne du devis
  With Sheets("Devis")
    For J = 20 To .Range("A108").End(xlUp).Row
    Set r = pl.Find(.Cells(J, 1), , xlValues, xlWhole)
     .Cells(J, 2) = r.Offset(0, 1)  'MAJ Libellé
     .Cells(J, 3) = r.Offset(0, 2)  'MAJ UM
     .Cells(J, 5) = r.Offset(0, 4)  'MAJ Prix
     .Cells(J, 6) = .Cells(J, 4) * .Cells(J, 5) 'MAJ Montant HT ligne
    Next J
  '*** Mise à jour des totaux du devis
    Set cel = .Range("D:D").Find(what:="T.V.A", LookIn:=xlValues, lookat:=xlWhole)
    cel(0, 3) = Application.WorksheetFunction.Round _
    (Application.WorksheetFunction.Sum(.Range("F20:F" & (cel.Row - 2))), 2)        ' Montant HT
    cel(1, 3) = Application.WorksheetFunction.Round(cel(0, 3) * cel(1, 2), 2)     ' Montant TVA
    cel(2, 3) = Application.WorksheetFunction.Round(cel(0, 3) + cel(1, 3), 2)     ' Montant TTC
  End With

@+

Gael

 

[Arpette]

Re : VBA Mettre à jour ma feuille Devis

Bonsoir Gael, le Forum,
un grand merci à toi, c'est exactement ce que je souhaitais, pas un bug, nickel.
Encore un grand merci.
@+
Pascal.