XL 2016 créer un classeur qui gèrent des avenants de contrat

[Linda42]

Bonjour,

J'ai besoin de vous pour créer un fichier me permettant de gérer des avenants :

- A l'ouverture du classeur, une seule feuille d'acceuil avec un use forme donnant la possiblité soit de créer des avenants soit de consulter les avenants pécédement crées, ou consulter un tableau récapitulatif des avenants qui s'afficherait si on clique su ce choix (Feuille ListeAvenant).
Ci-joint un fichier avec ce tableau récap, qui reprendra toutes infos à saisir dans le use forme (ce tableau permet de savoir ce que l'utilisateur devra saisir pour la création de ou des avenants).
Une fois que l'utilisateur aura valider la création de l'avenant dans le use forme, les informations devront se reporter dans le tableau récap (Feuille liste avenant) et générer l'avenant à partir du modèle de la feuille (Avenant N°XXXX). Il y a aura donc autant de feuille que d'avenant créer.
J'ai mis en rouge une formule qui reprends les emplacements des différents items dans le modèle de document;
Je ne suis pas trés douée avec les vba, mais votre aide me sera d'une grande utilité.

Merci beaucoup

 

[vgendron]

Bonjour

déjà, il faut que tu supprimes ton fichier, pour le remplacer par une version sans donnée personnelle (Numéro de tel, adresses mail, nom ....)

ensuite, il faudrait que tu traces le formulaire dont tu as besoin:
tu positionnes juste les diférents controles (boutons - listes - textbox) en expliquant ce que tu attends de chacun d'eux

 

[Linda42]

Bonjour

déjà, il faut que tu supprimes ton fichier, pour le remplacer par une version sans donnée personnelle (Numéro de tel, adresses mail, nom ....)

ensuite, il faudrait que tu traces le formulaire dont tu as besoin:
tu positionnes juste les diférents controles (boutons - listes - textbox) en expliquant ce que tu attends de chacun d'eux

Bonjour,

Merci pour ta réponse.

Ci-joint donc mon formulaire.
La feuille Item 1 permet de commencer à traduire en language VBA, ce que je souhaiterais.

Je continue pour les autres items mais j'ai pensé qu'il était peut être judicieux d'y aller par étape. Ça permet à ceux qui souhaitent m'aider à commencer et dans l'attente, je continu à définir ce que je souhiate pour les autre item.

J'espère que mes demandes sront assez claire ;-)
Merci encore

 

[vgendron]

Hello
la méthode étape par étape me semble plutot sage au vu de la quantité de code à fournir..

je te propose une première ébauche en PJ

1) le tableau de la feuille "ListeAvenants" est une table strucuturée (Tab_Avevants)
cela impose des noms de colonnes UNIQUES==> d'ou certaines que j'ai été obligé de renommer
vérifie que ces noms correspondent bien à ce qu'ils représentent

!! ces noms sont utilisés tels quels dans le code

2) sur ton formulaire, j'ai supprimé des boutons "Retour Home" pour n'en garder qu'un seul, et je l'ai déplacé pour qu'il soit visible sur tous les multipages
idem pour le bouton quitter
idem pour les boutons Suivant et précédent

pour la date, le formulaire que tu avais préparé plante à cause d'une fonction "Lundi" inconnue
plutot que d'aller le débugger, j'ai préféré le supprimer et remplacer par un autre formulaire beaucoup plus simpliste qui utilise le DTPicker de VBA

Je te laisse regarder cette version préliminaire

 

[vgendron]

Quelques remarques sur ton projet
si jai bien compris, un ou plusieurs avenants peuvent etre créés pour le numéro du marché que tu choisis dans le textbox "N° Marché dans l'onglet "Informations marché public ou de l'accord-cadre" du multipage2

mais. dans la feuille "ListeAvenants" je ne vois pas de colonne indiquant le numéro du marché
au contraire. le numéro de marché n'apparait qu'une fois en E1
==> Comment gères tu les différents marchés?
j'imagine que tu as un système de numérotation
NumMarché - NumAvenant ??

Dans ton formulaire, j'ai l'impression qu'il y a des "menus" redondants.
Sur le Multipage 1 principal, on a
page 1: CréerModifierSupprimer
Page2: Consulter
Pager3: Editer==> celui ci fait déjà doublon avec le "modifier" du page 1

sur le Multipage 2 on a:
Page1: un option bouton "Créer avenant" qui permet de saisir les données sur les page2 et 3 du multipage2
un option bouton "Modifier un avenant"==> Doublon avec le "Modifier" du page1 multipage1
un option bouton "Consulter un avenant" qui fait doublon avec la page 2 du multipage1

je pense qu'il faut simplifier ca au risque d'avoir des codes dans tous les sens qui font la meme chose..

 

[Linda42]

Quelques remarques sur ton projet
si jai bien compris, un ou plusieurs avenants peuvent etre créés pour le numéro du marché que tu choisis dans le textbox "N° Marché dans l'onglet "Informations marché public ou de l'accord-cadre" du multipage2

mais. dans la feuille "ListeAvenants" je ne vois pas de colonne indiquant le numéro du marché
au contraire. le numéro de marché n'apparait qu'une fois en E1
==> Comment gères tu les différents marchés?
j'imagine que tu as un système de numérotation
NumMarché - NumAvenant ??

Dans ton formulaire, j'ai l'impression qu'il y a des "menus" redondants.
Sur le Multipage 1 principal, on a
page 1: CréerModifierSupprimer
Page2: Consulter
Pager3: Editer==> celui ci fait déjà doublon avec le "modifier" du page 1

sur le Multipage 2 on a:
Page1: un option bouton "Créer avenant" qui permet de saisir les données sur les page2 et 3 du multipage2
un option bouton "Modifier un avenant"==> Doublon avec le "Modifier" du page1 multipage1
un option bouton "Consulter un avenant" qui fait doublon avec la page 2 du multipage1

je pense qu'il faut simplifier ca au risque d'avoir des codes dans tous les sens qui font la meme chose..

Bonjour

Merci beaucoup pour ton aide précieuse.

En fait, un classeur = un marché (un seul numéro de marché). Ce classeur permet de gérer l'ensemble des avenants de ce marché. Il n'y a donc pas de choix possible à faire dans la textbox "N° Marché dans l'onglet "Informations marché public ou de l'accord-cadre" du multipage2. C'est une saisie unique. Pour un autre marché, ça sera un autre classeur. D'où le fait que le numéro de marché n'apparraissent qu'une fois en E1.
(mais l'idée d'un outil permettant la gestion de tous les avenants de tous les marché est pas mal)

On a effectivement un autre fichier permettant de gérer l'ensemble des marchés, et nous récupérons le système de numérotation avec ce fichier.
La mise en place de ce fichier est pour faciliter et éviter des erreurs de resaisi des avenants. A chaque modification de marché, on reçoit un mail avec des infos en vrac pour mise en place de l'avenant conrrespondant. L'idée est que chaque acheteur utilise cet outil, qui générera l'avenant automatiquement, et nous n'aurons plus qu'à valider le partie juridique.

A ce jour aucune numérotation automatique des avenants. A chaque demande, nous consultons les documents du marché dans un dossier informatique pour vérifier s'il existe déjà un avenant, et nous numérotons en fonction des infos.

Je suis d'accord pour simplifier. Il est vrai que je suis tellement dans le document que j'ai certainement pas assez de recul pour voir ce qui ne va pas et toutes critiques, conseils sont les bienvenus. C'est comme ca que j'apprends.

Merci pour les améliorations apportées dans le formulaire. J'ai testé ton document : ci-dessous mes remarques :
- Je pense avoir oublié un item important c'est la possibilité de supprimer un avenant (erreur de saisie, voir annulation de la modification du marché). Je l'ai donc rajouter dans le Use form et fera l'objet d'un descriptif comme je l'ai fait pour l'item 1)
- Dans le USe forme, "objet du marché", le calcul du montant durée total marché HT et TTC, ne se font pas. Est-ce qu'il n'est donc pas possible de le faire à ce niveau ? (Dans ce cas je l'enverrai du formulaire pour le laisser uniquement dans tableau (à voir ensuite comment on répercute l'info dans l'édition de l'avenant.
-Sur la page suivante, "Informations avenant"; l'information N°avenant n'est pas renseignée. Peut que cela fait doublon et nous devrions le laisser uniquement sur cette page et le suprimer de la page 1, objet du marché? Après tout c'est plus logique qu'il apparraisse à cet endroit.
- Idem pour les calculs de cette page. Si ce n'est pas possible qu'il apparraissenet directement dans le useforme (puis reporté dans le tableau), je pense les enlever du formulaire et les laisser les formules uniquement dans la table.
- j'ai l'impression que dans ListeAvenant, les colonnes X à AA ne sont pas nécessaires : en effet, soit j'ai pas d'avenant et le montant actuels correspondent au montant initial du marché, soit il y a un avenant et le montant actuel correspond au nouveau montant du marché. Je pense qu'on peux donc supprimer ces colonnes. Ne sachant pas quelle incidencence cela peut avoir sur tes codes vba, je prèfère ne pas le faire.
- Une fois que, j'ai validé ma saisie, je m'aperçois que :
* le montant HT de l'avenant, n'est pas reporté en colonne AG
* pas de report de la formule en colonne U (d'ailleurs, la formule est fausse, je l'ai modifié dans le info f ITEM1)
* pas de report de la formule non plus en colonne W
* pas de report de le l'objet de l'avenant dans AB
* pas de report de la date d'effet en colonne AD
* pas de report de la TVA avenant en colonne AF
* pas de report du HT par mois dans AG

Merci beaucoup. En attendant, je continue à définir mon besoin. ;-)

Cdt

 

[vgendron]

Hello

OK, noté, pour le marché unique
est ce que tu souhaites rester comme ca? ou prévoir dès maintenant la possibilité de 1) choisir un marché 2) créer les avenants?===> un seul classeur pour tous les avenants de tous les marchés
==> ca veut dire que la feuille "ListeAvenants" a juste une colonne de plus avec "Numéro de marché" et tous les acheteurs utilisent le meme classeur==> et donc ont tous accès à tous les marchés..

pour tous les "problèmes" de non calculs que tu as notés: pas de panique, c'est normal, les calculs n'ont pas été codés

 

[Linda42]

Hello

OK, noté, pour le marché unique
est ce que tu souhaites rester comme ca? ou prévoir dès maintenant la possibilité de 1) choisir un marché 2) créer les avenants?===> un seul classeur pour tous les avenants de tous les marchés
==> ca veut dire que la feuille "ListeAvenants" a juste une colonne de plus avec "Numéro de marché" et tous les acheteurs utilisent le meme classeur==> et donc ont tous accès à tous les marchés..

pour tous les "problèmes" de non calculs que tu as notés: pas de panique, c'est normal, les calculs n'ont pas été codés

C'est sur que c'est l'idéal de faire un seul fichier, mais la problématique que j'ai c'est que je gère les marchés d'établissements différents : une partie des acheteurs ont accès à une ressource partagée où effectivement il pourrait accéder à ce fichier sur un serveur interne à mon établissement. Mais beaucoup d'autres dépendant d'établissements différents, n'ont pas à accès à ce serveur. Et donc impossible pour eux de modifier le document commun, cela nous obligerais à resaisir si nous souhaitons tout mettre dans le même document . J'avoue que je suis face à un dileme car ton idée est génial d'un point de vue gestion globale des avenants. Mais comment le gérer avec des établissements différents?
D'où l'intérêt d'avoir mis en place un classeur = un avenant, qui sera enregistrer dans le dossier du marché (puisque nous centralisons l'ensemble des marchés (ressource partagé pour mon établissement, sharepoint pour les autre). Ou alors il faudrait que je mette en place un formulaire de saisie type tableau (Liste d'avenant par exemple) que chaque acheteur devra fournir pour mise en place d'un avenant et que mon service importera dans le calsseur commun pour éditer les avevants et envoi au fournisseur. Ce serait finalement l'outil de travail de mon service. je sais pas qu'en penses tu?

Merci beaucoup

 

[vgendron]

Ok, on va éviter de casser toute ton organisation dans un premier temps.
je te propose ceci
1) je présume qu'un acheteur peut avoir plusieurs marchés à gérer==> donc plutot que d'avoir plusieurs classeurs (un par marché) à gérer, on peut n'avoir qu'un seul classeur dans lequel il selectionne le marché concerné par l'avenant

==> ca veut dire que au final tous les acheteurs te fourniront leur classeur qu'il te faut compiler==> c'est (je pense) ce que tu fais déjà==> pour ca, l'outil "Power Query" intégré à excel est très fort et simple

==> ca veut dire aussi que dans le classeur, on a "juste" une colonne à ajouter: Numéro de marché
==> si l'acheteur est OK pour regrouper tous les marchés dans un seul classeur, c'est impec
==> s'il préfère toujours avoir un classeur par marché==> ca reste ok, puisque l'ajout de la colonne supplémentaire ne le perturbera pas.

2) je pense qu'il faut distinguer deux parties dans l'application
==> 1ere partie: Création - Modification - Suppression des avenants ==> Correspond à remplir les tableaux
==> 2ème partie: l'édition (dans le sens Impression) des avenants ==> Correspond au remplissage et impression du "Modèle Avenant"

3) pour les colonnes à supprimer: ca ne me pose aucun souci MAIS question sur l'origine et utilité de ces colonnes..
quand un marché est ouvert: tu as des montants Initiaux "Mont_Init"
si tu fais un premier avenant==> tu as de nouveaux montants
mais si tu fais un second avenant==> les "nouveaux montants du premier avenant deviennent les "Mont_Ini" et tu recalcules de nouveaux Montants.. et ainsi de suite
l'ennuie. c'est que tu "perds" l'information des montants init à l'ouverture du marché..
si l'acheteur consulte le tableau excel, il va devoir faire une petite gymnastique pour retrouver les montants avant le tout premier avenant
le formulaire, quant à lui, peut très facilement reafficher les montants avant tout avenant..

Je pense que c'est plus simple de supprimer les colonnes (ca fait moins de controles à remplir)
mais à toi de décider ce qui convient le mieux. selon l'utilisation qui est faite du tableau.

 

[Linda42]

Ok, on va éviter de casser toute ton organisation dans un premier temps.
je te propose ceci
1) je présume qu'un acheteur peut avoir plusieurs marchés à gérer==> donc plutot que d'avoir plusieurs classeurs (un par marché) à gérer, on peut n'avoir qu'un seul classeur dans lequel il selectionne le marché concerné par l'avenant

==> ca veut dire que au final tous les acheteurs te fourniront leur classeur qu'il te faut compiler==> c'est (je pense) ce que tu fais déjà==> pour ca, l'outil "Power Query" intégré à excel est très fort et simple

==> ca veut dire aussi que dans le classeur, on a "juste" une colonne à ajouter: Numéro de marché
==> si l'acheteur est OK pour regrouper tous les marchés dans un seul classeur, c'est impec
==> s'il préfère toujours avoir un classeur par marché==> ca reste ok, puisque l'ajout de la colonne supplémentaire ne le perturbera pas.

2) je pense qu'il faut distinguer deux parties dans l'application
==> 1ere partie: Création - Modification - Suppression des avenants ==> Correspond à remplir les tableaux
==> 2ème partie: l'édition (dans le sens Impression) des avenants ==> Correspond au remplissage et impression du "Modèle Avenant"

3) pour les colonnes à supprimer: ca ne me pose aucun souci MAIS question sur l'origine et utilité de ces colonnes..
quand un marché est ouvert: tu as des montants Initiaux "Mont_Init"
si tu fais un premier avenant==> tu as de nouveaux montants
mais si tu fais un second avenant==> les "nouveaux montants du premier avenant deviennent les "Mont_Ini" et tu recalcules de nouveaux Montants.. et ainsi de suite
l'ennuie. c'est que tu "perds" l'information des montants init à l'ouverture du marché..
si l'acheteur consulte le tableau excel, il va devoir faire une petite gymnastique pour retrouver les montants avant le tout premier avenant
le formulaire, quant à lui, peut très facilement reafficher les montants avant tout avenant..

Je pense que c'est plus simple de supprimer les colonnes (ca fait moins de controles à remplir)
mais à toi de décider ce qui convient le mieux. selon l'utilisation qui est faite du tableau.

1)Ok, on va faire cela. On rajoute la colonne N° Marché.
La subtilité c'est que les acheteurs fournissent les informations en vrac dans un mail lambda au service juridique qui resaissent dans un formulaire word. Mon idée est de leur faciliter la tache avec un outils facilie à utiliser car malheureusement l'utilisation d'excel est trés faible. Autant te dire que si je commence à leur dire Power Query, je vais avoir le droit à "on va reste à la méthode ancienne et resaisir.

2)Ok avec cela, sachant que le projet que je suis en train de définir impliquera soit une impression en pdf + enregistrement sous etc... soit une possibilité de modifier l'avenant avant impression (car dans le use forme, je n'ai pas prévu toutes les caractérisiques existantes dans le cadre d'avenant.) D'où l'intérêt de pouvoir modifier une version excel avant une impression papier ou pdf. Jes laisserai justement des lignes vides dans le modèle permettant ainsi aux juristes de saisir d'autre éléments si besoin pour l'avenant

3)Pour les colonnes à suprimer : Les montants initiaux sont à conserver tout le long de la durée du marché (d'ailleurs à chaque fois qu'il y a un avenant, il y l'information du montant initial du marché, ainsi que l'information liée au différents avenant :
==>Avenant 1 : information du montant initial du marché + information du nouveau montant
==>Avenant 2 : information du montant initial du marché + information actuel du marché (donc Avenant 1) + information du nouveau montant
==>Avenant 3 : information du montant initial du marché + information montant avenant 1 + information actuel du marché (donc avenant 2) + du info nouveau montant
==>Avenant 4 : information du montant initial du marché + information montant avenant 1 + + information montant avenant 2 +information actuel du marché (donc avenant 3) + du info nouveau montant
et ainsi de suite....les nouveaux montants de chaque avenant deviennent les montants nouveau. Seul le montant initial ne change pas. il faudrait peut
C'est pour cela que je me posais la question de la pertinence de conserver ces colonnes, car pour chaque nouvel avenant, les montants actuels = nouveau montant du dernier avenant en vigueur). Et comme tu le dis, je ne souahitent pas perdre l'information montant Init

"le formulaire, quant à lui, peut très facilement reafficher les montants avant tout avenant.."
C'est exactement ce que je souhaite.

Si elles ne sont donc pas utilent, ont peut suprimer.

Cdt
Linda

 

[Linda42]

vgendron,​

Ci-joint le fichier avec la suite du descritpif nomé Item2.

J'en ai profité pour avancer sur l'enregistrement sous PDF en insérant un module avec un code que j'ai récupérer d'un autre fichier.

Dans l'attente de te lire.

 

[vgendron]

J'ai un souci pour identifier certaines colonnes à mettre dans les textbox
sur le Multipage 2 - Page 2 (Informations Avenant)
en bas, il y a 6 textbox avec des montants
à gauche, des montants "Actuels" ??
à droite les nouveaux montants

peux tu me dire pour chacun d'entre eux à quelle colonne ca correspond et lequel (ou lesquels) l'acheteur doit saisir, et donc lesquels sont automatiquement calculés
pour les actuels.. ce sont les montants du dernier avenant déjà validé ou ce sont les montants initiaux du marché (ceux qui sont déjà affichés dans le multipage "Informations marché public dans le frame de droite ?

 

[Linda42]

J'ai un souci pour identifier certaines colonnes à mettre dans les textbox
sur le Multipage 2 - Page 2 (Informations Avenant)
en bas, il y a 6 textbox avec des montants
à gauche, des montants "Actuels" ??
à droite les nouveaux montants

peux tu me dire pour chacun d'entre eux à quelle colonne ca correspond et lequel (ou lesquels) l'acheteur doit saisir, et donc lesquels sont automatiquement calculés
pour les actuels.. ce sont les montants du dernier avenant déjà validé ou ce sont les montants initiaux du marché (ceux qui sont déjà affichés dans le multipage "Informations marché public dans le frame de droite ?

Ce sont les montants de l'avenant en cours de saisie. et deviendront les montants "actuels" en cas de mise en place d'un autre avenant.
Les montants initiaux du marché sont sur Information marché public dans la frame de droite. Ces information seront fixes et seront toujours qualifiés de montant initiaux. Seul les nouveaux montant pourront être modifier au fur et a mesure des avenants
Dans le cas d'un marché à avenants, les informations :
==>Avenant 1 :
information du montant initial du marché + information du nouveau montant (Avnt 1)
==>Avenant 2 :
information du montant initial du marché + information actuel du marché (Avnt 1) + information du nouveau montant (Avnt 2)
==>Avenant 3 :
information du montant initial du marché + information montant Avnt 1 + information actuel du marché Avnt 2 + du info nouveau montant
==>Avenant 4 :
information du montant initial du marché + information montant avt 1 + + information montant avnt 2 +information actuel du marché avnt 3 + info nouveau montant Avnt 4
J'espère que c'est un peu plus claire


A gauche
Tbx_CoutMensuelAvHT ==> saisie par l'acheteur et correspondant à l'incidence HT par mois du nouvel avenant (cette info est a reporte dans la colonne
"Montant HT de l'avenant par mois : " (AG) de la ListeAvenant et sert à calculer l'incidence surle reste à courrir du marché - Pas de report dans l'impression)

Tbx_MontantAvenantHT ==> calcul automatique à reporter dans la colonne "Montant HT de avenant reste à courrir :" (AH) - (Formule : =AG*AE)

Tbx_MontantAvenantTTC ==>calcul automatique à reporter dans "Montant TTC de avenant reste à courrir :" (AI) - (formule : =(AH*AF)+AH)

A droite
Tbx_New_CoutMensuelAvHT ==> à supprimer

Tbx_New_MontantAvenantHT ==> calcul auto. - a reporter dans la colonne "Nouveau montant HT du marché :" (AK) - Formule =AH+Y)

Tbx_New_MontantAvenantTTC ==>calcul auto. - a reporter dans la colonne "Nouveau montant TTC du marché :" (AL) - Formule =(AK*AF6)+[AK)
Merci

 

[vgendron]

Une autre question
la toute première fois, lorsque le tableau est vide.. il n'y a aucun marché de défini==> on ne peut donc à priori pas saisir d'avenant
Comment les acheteurs gèrent ils ce cas?

Dans ce que je suis en train de faire, lorsque l'acheteur clique sur "Créer/modifier/supprimer" un avenant, s'il n'y a pas de marché, l'application demande de saisir les infos avant de poursuivre.
la première ligne du tableau est donc enregistrée aec les infos et un "numéro d'avenant 000" (ce qui peut permettre d'identifier facilement les montants initiaux.

 

[Linda42]

Une autre question
la toute première fois, lorsque le tableau est vide.. il n'y a aucun marché de défini==> on ne peut donc à priori pas saisir d'avenant
Comment les acheteurs gèrent ils ce cas?

Dans ce que je suis en train de faire, lorsque l'acheteur clique sur "Créer/modifier/supprimer" un avenant, s'il n'y a pas de marché, l'application demande de saisir les infos avant de poursuivre.
la première ligne du tableau est donc enregistrée aec les infos et un "numéro d'avenant 000" (ce qui peut permettre d'identifier facilement les montants initiaux.

A l'ouverture du tableau, les acheteurs sont censés nous transmettre le numéro de marché concerné pour l'avenant souhaité. Sans quoi, nous ne pouvons pas faire l'avenant. D'ailleurs, la mise en place d'un tel outil est aussi pour faire ganer du temps, car les juristes, à réception d'une demande vont à la pèche des informations des avenant précdents dans différent dossier. l'interet est donc d'avoir les informations sur tous les avenants du marché en un seul endroit de façon à avoir l'historique d'un coup.

Ton idée est excellente de conditionner la poursuite de la saisie à l'information du numéro de marché.