Xl/Vba: Alerte sur critères TCD

Question

Bonjour 


Je vous joins ci apres un fichier pour lequel j'aurai besoin de pistes, voir eventuellement de code.

Le principe de ce fichier est le suivant: 

Nous recevons des demandes de paiement papier et les inserons dans ce fichier pour assurer un suivi 

Le souci que j'ai est que  pour passer par ces modes de règlements manuels ,  il faut que le fournisseur ai au maximum de 6 factures dans l'année et que le montant total de ces factures n'excède pas 10000€ ( meme si il y a moins de 6 factures)

J'ai pensé a un TCD pour faire l'analyse par fournisseur pour obtenir la somme du nombre de facture( qui correspond à la somme de la colonne H de l'onglet SUIVI) et le montant total des factures (somem de la colonne I de l'onglet SUIVI)

Le probleme est que cela implique une actualisation systématique du TCD pour vérifier les critères et chercher dans le TCD ( celui de démo joint plus bas reprend juste quelques info de base  la liste est en realité beaucoup plus longue avec un grand nombre de fournisseur


Le but recherché idéalement, serait  que Excel nous alerte lorsque l'on procède a la saisie si un même fournisseur atteint les 6 factures ( cumul des Nb ) ou si le montant dépasse les 10000€ à l'aide d'un  message d'alerte type : 

"Attention le fournisseur a dépassé le nombre de facture autorisé pour le PLR. Faire une demande de référencement"
et / ou 
"Attention le fournisseur a dépassé le montant maximul de règlement autorisé pour le PLR. Faire une demande de référencement"

ou meme plus simplement quelque soit la raison : 
"Attention le fournisseur n'est plus éligible au PLR. Veuillez procéder à son référencement"


La contrainte supplémentaire, au vu du TCD c'est que cette analyse et les alertes ne doit paraitre que lorsque l'on utilise un paiement a un fournisseur ( donc  dans Type PLR de l'onglet suivi, uniquement lorsque Règlt Fournisseur est sélectionné)


Et là j'avoue que je n'ai aucune idée de comment gérer le truc, mes compétences en Vba  sont pas aussi évoluées

Si quelqu'un pouvait me donner des pistes, et eventuellement des idées de codes  pour que je puisse résoudre ce problème , ca serait sympa 


Merci d'avance a ceux qui se pencheront sur le sujet pour m'aider 


Bonne journée 


Fichier Test : https://www.cjoint.com/?0ItroMpQbNo


Contrainte a prendre en compte : je suis sous excel 2002


Configuration: Windows XP / Internet Explorer 8.0

7808622H · Answer

bonjour tout le monde, 

bon je sais que remonter le sujet est pas tip top, mais j'ai beau faire des recherches a droite a gauche, j'arrive a rien ,  donc si y'a une bonne âme  super doué en Vba  qui peut m'aider ..... 

Bon week end

eriiic · Answer

Bonjour,     

J'ai vu ton appel au secours ;-)      
On peut faire une fonction personnalisée.     

Seulement si tu veux vraiment une mise à jour au fil de l'eau il faut la faire 'volatile' et elle est réévaluée à chaque saisie dans la feuille.      
Si tu as beaucoup de ligne ça risque d'être gourmand en ressources. Si c'est le cas dis-le et on fera plutôt un sub que tu appelleras au coup par coup pour faire la mise à jour.     
Ou bien tu établis une liste des fournisseurs dans Données et met une seule fois la fonction à coté. La mise à jour sera à faire manuellement (ou une petite macro mais bon, il n'y a pas grand chose à ajouter) en cas d'ajout de fournisseur, et tu récupères le PLR du fournisseur par un recherchev(). Ca allègera considérablement les recalculs.  

Function PLR(Fournisseur As String) As Boolean     
    Dim lig As Long, tabl As Variant     
    Dim nbFact As Long, montant As Double     
    Application.Volatile     
    tabl = Worksheets("SUIVI").Range("E5:I" & Worksheets("SUIVI").Cells(Rows.Count, 7).End(xlUp).Row)     
    For lig = 1 To UBound(tabl)     
        If tabl(lig, 1) = "Règlt Fournisseur" And tabl(lig, 3) = Fournisseur Then     
            nbFact = nbFact + tabl(lig, 4)     
            montant = montant + tabl(lig, 5)     
        End If     
    Next lig     
    PLR = nbFact > 0 And nbFact <= 6 And montant <= 10000     
End Function
Syntaxe : = PLR("nom_du_fournisseur")     
Ex: = PLR(G5)     
J'adresse directement les colonnes dans SUIVI, si elles bougent il faut adapter la macro.     

Après tu l'utilises comme tu veux : soit une colonne dédiée ou bien dans une MFC.     

Evite la fusion des colonnes, ça fout toujours le bazar. J'ai dû te mettre le titre 'centré sur plusieurs colonnes'  pour que la MFC fonctionne.     
https://www.cjoint.com/?BIxq1cQdRM3     

eric     

PS: j'ai supposé que nb était un nombre de factures...    

Jamais tu ne répondras à un mp non sollicité...     
Bon, ça c'est fait.

7808622H · Answer

Bonjour Eric et merci de m'aider ( c'ets pas la premiere fois en plus  ;-) ))


Concernant le quantitatif de données  varie en effet, si je me base sur ce que j'ai deja cette année ,  j'ai en gros 700 lignes excel  dans le tableau 


Par contre en reprenant ton fichier j'ai essayer de tester de remettre des lignes   et  soit j'ai pas pigé le fonctionnement soit je suis totalement blond ( bon ok c est le cas) 

Nb est en effet le nombre de facture, mais c est le cumul qui ne doit pas depasser 6  par an et ou le cumul du montant qui ne doit pas depasser 10 000 €


En janvier on peut avoir 1 facture APF de 2000   en juillet 3 autres facture qui feraient au total 8500   ( mais la colonne nb est saisie manuellement ca n'est pas un cumul, juste la saisie du jour )
 
Dans ce cas je devrais avoir une alerte
 
Je t'explique le contexte générale,   dansm a boite on travaille avec un systeme informatique  qui a sa base fournisseur

Afi nde ne pas  alourdir le ficheir fournisseur on accepte de payer, par chèque ou virement manuel ( c'est un autre systeme informatique qui gère) les fournisseurs occasionnels a concurrence de 6 factures maximales ou 10 000€ maximum par an

Si ces  critere sont depassés on doit referencer le fournisseur dans la base 

voilou est ce que cela t'aide un peu ?

J'ai acces a CCM au bureau ainsi qu'a cjoint, et vu que j ai franchement pas grand chose a faire en ce moment je pourrais voir regulierement si tu reponds tardivement

7808622H · Answer

Re bonsoir Eric

alors ce que j'ai pu constater  si je ne me trompe pas ( arrête moi si c'est le cas )

- lors des saisies tant que les critères ne sont pas atteint, le nom du fournisseur passe en vert
- Dès que les critères sont atteint  la couleur de fond disparait ( d'ailleur  pour le nombre de facture c'est supérieur a 6 j'ai fait la modif dans le code et modifié en inferieur a 7)


Bon par contre ca m'embête un peu, car du coup la mise en évidence revient justement a ne rien y avoir comme trame de fond. Ca passe un peu inaperçu avec les remboursements agents par exemple.

Donc là ca fait justement l'inverse de ce que je cherche : 
- ca passe en vert quand c'est bon et ca n'active rien quand c'est faux


De plus pourrais tu m'expliquer la colonne K ?? je n'arrive pas a identifier  "=plr", je vois bien que ca renvoi au nom de fournisseur de la ligne, mais je ne saisie pas


A la limite, je préfère encore un petit bouton a cliquerr  une fois les saisies terminées, mais qui me ferai vraiment un message d'erreur qui s'affiche, là en l'état, c'est juste l'inverse de ce que je cherchais..

Bon je sais suis chiant , par contre  dans le fichier que j'ai envoyé, j'ai juste un tit souci, normalement la colonne k devrait etre utilisée pour indiquer une date de paiement ( j avais pas mis sur le fichier demo, je pensais pas que ca serait génant), par contre c'est totalement libre après


Donc voilou mes petites constatations 

je te rejoins le fichier  avec la modif sur le critere du +6  enfin du <7

https://www.cjoint.com/?0IxvUKQeAUO 


Merci d'avance 

Bonne soirée 

Céd 
 
--

7808622H · Answer

Bonjour Eric


alors mes constats sur le fichiers

- En premier lieu j'ai modifié pour plus de clarté  la liste des fournisseurs en mettant des numerotations ca sera plus simple pour s'y retrouver pour monter le fichier 
- Comme je ne sais pas exactement ou pointent les  formules et codes, penses tu qu'il serait possible de faire une feuille dédiée pour les fournisseurs ?

Concernant les rsesultats des tests que j'ai fait : 

-régulièrement la colonne L  laisse apparaitre des N/A a la place des VRAI / FAUX
-Si tu filtre sur Agent-001 qui ne devrait pas pointer en rouge, tu verras que certains le sont (ligne 9 et d'autres selon les réouvertures de fichier)
-Si tu filtre sur fournisseur-0001 , j'ai un nombre de nb supérieur a 6 et  2 lignes sur 4 se mettent en rouge  alors qu'elle pointent en FAUX,  et les deux lignes qui pointent en vrai reste en blanc alors qu'elles devraient etre toutes les 4 en rouge 


Voila a vu de nez c'est ce que j'ai pu voir ... j'espère que cela te donneras les pistes necessaires

Ci joint le fichier utilisé : https://www.cjoint.com/?3IEqouDxjVH 

Bon courage et merci

Céd

Xl/Vba: Alerte sur critères TCD

5 réponses

Votre réponse

Discussions similaires

Newsletters