Mise à jour des stocks après facturation

[sokalbr]

Bonjour à toutes et à tous,

Je suis actuellement en Terminale STG (Comptabilité et finance des entreprises) et mon projet pour le bac repose sur l'automatisation de facture, sous excel. Le classeur se présente sur trois feuilles, une feuille facture (Modèle de facture devant être lié avec les deux autres feuilles), une feuille clients (Refcli, Nom, Adresse, CP, Ville, Délai réglement), et une feuille produits (Refprod, Désignation, Prix Vente HT, Stock).

La consigne impose que uniquement les données en jaune sur la feuille (Refclient, RefProduit et Quantité) peuvent être saisies.

Ci joint mon travail qui est donc presque fini, mais dans l'histoire de l'améliorer encore, j'aimerai pouvoir faire une mise à jour des stocks une fois que les quantités commandées sont rentrés sur la facture. Je n'ai malheureusement aucune idée de comment faire et si cela est même possible alors auriez vous des idées ?

Les conseils sur le travail déjà fait serait très apprécié !

En vous remerciant d'avance,

Valentin

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Bonjour et bienvenu sur le forum Sokalbr

Pour ma part je laisserais tombé la partie "gestion des stocks", car ce point ne figure pas dans l'ennoncé du problème.

Cela risque d'être considéré comme du hors sujet et pourrais te désservir. Des remarques du genre :
"si tu n'avais pas perdu de temps à faire ce qui n'est pas demandé, tu aurais pu te concentrer d'avantage sur le reste et ainsi ne pas faire d'oublis et/ou d'erreurs"

Donc quand on peut éviter, autant éviter de se mettre à dos un examinateur, et profite de ce temps pour améliorer ton fichier (tout en restant dans le sujet).

Idées d'améliorations onglet Facture :
-> pour les cellules : F24, F26, F32 et F33 rajouter la fonction ARRONDI(...,2)
Cela te permettras d'arrondir à deux chiffres après la virgule tes résultats. Cela simplifiera la vie des comptables. Car sinon ils auront parfois HT + TVA <> TTC (à cause des fameux écarts d'arrondi).
(cela te permettras de montrer que tu maîtrise également cette fonction )

-> pas nécessaire, mais plus sympa : faire de même pour les cellules F14 à F13

-> cellule B6, au lieu de laisser l'utilisateur saisir le numéro client, mettre une liste de choix. Plusieurs avantages : plus sympa d'utilisation, plus professionnel. L'utilisateur ne pourras pas faire d'erreur de saisie : point primordiale car tes fonctions en D9, D10 et D11 en découle directement.
(cela te permettras de montrer que tu maîtrise également cette astuce )

-> colonne D : pour les quantités, tu peux mettres des conditions de saisie, pour t'assurer ainsi que l'utilisateur sera obligé de saisir une donnée réaliste. Par exemple l'obliger à saisir un nombre positif et entier? Pour cela, puisque tu es sur Excel 2007-2010 :
* onglet "Données"
* cliquer dans le ruban sur l'icone : "validation des données"
* onglet "option" dans la zone autorisé sélectionner... (puis te laisser guider par les questions, différents choix qui s'offre à toi, selon tes contraintes).

-> tu peux faire la même chose pour la cellule B7 : pour t'assurer qu'ils mettent un numéro de facture cohérent. Avec ces 2 méthodes, tu ne pourras jamais t'assurer qu'il n'y a pas de doublons au niveau des numéro de factures Mais comme ta question nous oblige à laisser cette zone "saisissable" et que tu ne sauvegarde pas la liste des numéros de facture déjà utilisé, impossible d'améliorer plus ce point.

-> a priori ta bordure entre A6 et B6 est fausse : elle ne colle pas avec l'aspect général du reste du document.

-> pourquoi tu as colorié la cellule D8 en blanc???? Ca n'apporte rien à ton fichier et en plus un oeil non avisé ne le remarquera même pas.

-> pour la TVA, ce sera toujours 19,6%? Car en France il existe également d'autres taux

C'est tout ce qui me saute yeux.

Bon courage.

A+

PS. : pour la "liste de choix", sache que la plus basique consiste à définir la zone (fixe) où se situe les données qui alimenteront la liste de choix. Ce qui oblige, si tu agrandi ta liste de clients, à insérer des lignes ENTRE la première et dernière ligne pour y insérer le nouveau client. Car si tu rajoute le client à la suite, il ne sera pas pris en compte.

Mais si tu regarde bien sur le forum, il existe une astuce (sans VBA), qui permet de définir une zone de cellule, dont la taille s'adapte AUTOMATIQUEMENT. Pour te mettre sur la voie, il faut utiliser comme précédement la liste de choix. Tout est dans la définition de la zone où se situe ta liste

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Re,

S'il te reste du temps, tu peux continuer à l'améliorer (tout en restant dans le cadre de l'exercice) ton fichier :

-> protéger les cellules où l'utilisateur ne doit pas aller (sans oublier de verrouiller l'onglet)

Une fois tout cela terminé, tu peux encore plus compliqué les choses en intégrant une macro. La réalisation du fichier sera donc plus complexe, plus intéressante, plus formatrice pour toi et permettra de rendre l'utilisation du fichier encore plus simple.

Pour mémoire, c'est bien ça, l'utilisateur ne doit pas toucher à la base client et produits? Car je n'ai vu aucune zone jaune sur ces onglets?

Dans ce cas tu peux évoluer ton fichier de la façon suivante (une fois toutes les modifications ci-dessus effectuées) :
-> faire un onglet "page d’accueil"
-> masquer tous les onglets sauf l'onglet "page d'accueil"
-> sur la page d'accueil deux zones
----1/ Administrateur(s) avec les boutons suivants :
--------* créer/supprimer/modifier un client
--------* créer/supprimer/modifier un article
--------* créer une facture
----2/ Utilisateur(s) avec les boutons suivants :
--------* consulter la liste des clients
--------* consulter la liste des articles
--------* créer une facture

-> faire une macro pour chacun des boutons ci-dessus. Sans oublier pour la partie Administrateur d'inclure un mot de passe pour s'assurer que l'Administrateur a bien les autorisations adéquats.

Tu vois? Pour améliorer ton fichier, il y a encore des tonnes d'astuces, d'idées qu'Excel met à ta disposition mais que tu n'as pas encore exploité

Bref, pas la peine de faire un hors sujet pour épater ton jury

Autre côté positif de ce projet, les macros à créer pour ton projet son assez basique, idéal pour apprendre le VBA en douceur. Tous les choses utiles à la réalisation de tout cela se trouve sur ce forum.

Il ne te reste plus qu'à retrousser tes manches.

Si tu as besoin d'un coup de main, si tu bloque sur un point, n'hésite pas à poser ta(es) question(s) sur le forum, il y aura toujours au moins une personne pour te répondre.

Bonne soirée

 

[sokalbr]

Re : Mise à jour des stocks après facturation

Bonsoir Excel-lent,

Tout d'abord un ÉNORME merci pour tous les conseils que vous m'avez donné !

J'ai un peu près réussi à les appliquer sur la facture malgré un soucis :

- Pour la liste de choix automatique, je n'ai pas trouvé sur le forum comment la zone peut s'élargir automatiquement. J'ai donc un peu tâtonné mais sans succès : lorsque je renomme la colonne A1 (ce qui m'a paru le plus pertinent) l'entête de la colonne s'affiche dans la liste de choix, ce qui pose problème. J'avoue que je sèche sur ce point là !

Pour la suite, je vais tâcher de m'en occuper dès demain. Je vous tiens au courant.

Très bonne soirée à vous

Valentin

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Salut Sokalbr,

Envoi nous ton fichier modifié, ainsi nous pourrons :
-> voir ton problème de ligne de titre
-> t'aider en même temps sur les autres points qui te posent soucis.
-> pour que ton groupe de cellules nommées s'agrandisse/diminue automatiquement il faut mettre la formule suivante à la place de ta zone "fixe" :

=DECALER('NomDeTonglet'!$A$2;1;;NBVAL('NomDeTonglet'!$A:$A)-1)

$A$2 : dans le cas où la première cellule de ta zone de cellule soit en A2. Sous-entendu en A1 c'est le titre de ta colonne

$A:$A : dans le cas où ta liste est dans la colonne A


PS. : ma réponse est vague, car je ne peux pas ouvrir ton fichier de chez moi (version Excel antérieur à la tienne). Pour les autres points, je pourrais y répondre demain entre midi et deux ou après demain. Tout dépendra quand tu déposeras ton fichier modifié. A moins que tu le mette sur le forum en version Excel 1997-2003 ? Ainsi TOUS les "répondeurs" pourrons lire ton fichier et donc te répondre. Ce qui te permettra d'avoir une(des) réponse(s) plus rapidement encore.

Bonne soirée

 

[sokalbr]

Re : Mise à jour des stocks après facturation

Bonsoir,

Voici un aperçu travail avec les macros. Je fignolerai niveau esthétique et en ajoutant les autres boutons demain car il se fait tard !
Mdp : 1234

Dites moi ce que vous en pensez Excel-lent

Merci encore pour votre aide



EDIT :
Mdp pour désactiver protection des cellules de la feuille "Facture" : rabote13
Classeur sous format 97-2003 rajouté

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Bonjour Sokalbr,

Je constate que tu te débrouille en VBA

Quelques modifications, pour améliorer ton code :

-> ta variable mdp est la même dans toutes les macros de ton "module1", donc au lieu de la définir dans chacune de tes macros, il te suffit de la définir une seule fois (en "public") (tu trouveras plus d'explication dans ta macro ).

-> éviter de mettre un mot de passe pour l'utilisateur (pour les boutons : "consulter liste produits", "consulter liste clients") car ils ne pourront que consulter. Ce qui ne représente aucun danger pour ton fichier. Donc pour lui éviter de risquer d'oublier le mot de passe "utilisateur", puisqu'inutile : pas la peine d'en mettre un pour eux (juste pour les boutons qui concerne QUE le(s) administrateur(s).

-> mettre un nom plus précis pour tes macros. Car une fois ton projet terminé, tu auras une macro "ConsultationListeClients", une macro "ModifierSupprimerAjouterUnClient", une macro ... Dans ce cas, comment savoir ce que fais la macro "clients"? Comme tu le sais peut-être déjà, comme l'on ne peut pas mettre d'espace dans le nom d'une macro, on met une majuscule au début de chaque mot. Ainsi une fois tous les mots collés, cela reste compréhensible

-> j'ai profité de l'occasion pour te rajouter la bordure "groupe" autour de tes deux groupes de boutons : ceux pour les "Utilisateurs", ceux spéciales "Administrateur(s)".


-> Comme tu peux le constater dans le fichier ci-joint : tu as utilisé les boutons "basique" proposé par Excel. Comme tu dois en mettre plein la vue à tes examinateurs, montrer l'ensemble de tes connaissances d'Excel (tout en restant dans le sujet ), l'idéal est de faire une forme (ou un dessin) et le personnaliser :
* coin arrondi
* ajout d'une zone de texte
* plusieurs styles de police : gras/non gras dans la zone texte
* maîtrise des bi-teintes
* ....
(dispo sur Excel 2003, alors Excel 2007-2010 n'en parlons pas!)

Une fois ton modèle de bouton réalisé, affecter à chacun d'eux la macro associée.

Tu peux par exemple prendre un code couleur pour les boutons "utilisateurs", et un autre pour les boutons "Administrateurs"

A la place du fond jaune (ou en plus), tu peux par exemple mettre le logo de ton école

J'ai vu que tu avais commencé à écrire une macro qui se déclenche à l'ouverture du fichier (chapeau bas), pour utiliser cette connaissance, tu peux par exemple mettre dans cette macro :
-> masquer TOUS les onglets : ormis l'onglet "Page d’accueil". Et mettre cette dernière en "page active". Ainsi si l'utilisateur (ou l'administrateur) a laissé par mégarde un onglet ouvert lors de la précédente sauvegarde, il(s) sera(ont) automatiquement masqué(s).

Comme nous avons conçu les choses, à chaque fois que l'administrateur cliquera sur un bouton spécial "Administrateur", il devra écrire le mot de passe! Tâche fastidieuse, répétitive et inutile.

Deux solutions :
-> demander dés l'ouverture du fichier (puisque tu connais ce genre de macro) : "Vous êtes Utilisateur ou Administrateur?" Si Administrateur, lui demander une fois pour toute le mot de passe "Administrateur". S'il est juste, un petit message disant une phrase dans ce genre : "mot de passe exacte, vous avez accès à toutes les fonctionnalités du fichier". Si le mot de passe est erroné : "mot de passe inexacte, vous n'avez accès qu'aux fonctionnalités "Utilisateur".

-> sinon dans la zone "Administrateur" : rajouter un bouton "se connecter/déconnecter en tant qu'Administrateur". Si la personne clique sur un bouton bleu, sans avoir au préalable cliquer sur ce nouveau bouton : message "Veuillez vous connecter en tant qu'Administrateur pour pouvoir accéder à cette fonctionnalité". Ainsi il mettra le mot de passe qu'UNE fois (du moins qu'une fois par ouverture de fichier)!

-> dans la liste de tes noms, tu n'as pas mis la version adapté de la formule envoyé tout à l'heure. Tu n'as pas réussi à trouver où la mettre? Ni comment l'adapter à ton cas? Dans ce cas, je t'ai mis la formule exacte : cf. le nom "TablCltJuste" dans le fichier ci-joint

PS. : nous sommes tous sur un pied d'égalité, tu peux me tutoyer
PS. 2. : il y a encore des tonnes d'astuces que l'on peut rajouter pour améliorer cela, mais je préfère m'arrêter là pour ne pas te noyer. Procédons par étapes. Faisons déjà tous ce que nous avons vu, et si nous avons le temps, on rajoutera d'autres améliorations par la suite.

 

[sokalbr]

Re : Mise à jour des stocks après facturation

Bonjour Excel-lent,

J'ai pu bien avancer sur le projet ce matin sur les points que tu m'as indiqué, ce qui m'a confronté à de nouveaux problèmes :

- Problème de taille : les feuilles "Produits" et "Clients" sont verrouillées et il m'est impossible de retrouver le code ! A moins que ça ce soit toi qui est voulu me faire une petite blague

- j'ai gardé le système de mot de passe par bouton car les deux autres méthodes que tu m'as proposé sont trop compliqué pour moi (tu pensais que j'avais des bases en VBA mais pas du tout j'ai farfouillé à droite a gauche pour trouver des bouts de codes et les incorporer/adapter sur le projet). A la rigueur j’essayerai de faire la première méthode, mais vraiment quand tout sera "clean".

Voilà le projet ci joint, je suis vraiment très content de voir ce qu'il est devenu par rapport au début, et cela grâce à toi !

Bonne journée,

Valentin


Edit : Je viens de finir l'auto verrouillage/déverrouillage des feuilles.

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Bonsoir Sokalbr,

Non, je n'ai pas changé les mots de passe. Excellent le jeux de mot concernant l'adresse de ta société! Et d'actualité lol

-> j'ai renommé ton onglet "Facture" : "Modèle Facture". A chaque fois qu'un "Utilisateur" souhaitera créer une facture : ta macro dupliquera cette onglet et le nommera "Facture 1", le suivant "Facture 2", le suivant ...

-> rajouter un bouton : Valider la nouvelle facture à mettre sur l'onglet "Modèle Facture". S'assurer qu'il n'apparaisse pas à l'édition : sélectionner (d'un simple clic gauche) le dessin qui te sert de bouton, sans bouger la souri faire un clic gauche, cliquer sur "format de la forme automatique", sélectionner l'onglet "propriété", puis décocher : "imprimer l'objet". Lorsque l'utilisateur cliquera sur le bouton, ta macro ira rajouter cette nouvelle facture saisie (du moins les données importantes), dans l'onglet "Cahier de Factures". Renommer l'onglet : "Facture 1", puis "Facture 2", puis ...

-> pour permettre de gérer la numérotation de tes factures, le nom de tes onglets, ... rajouter l'onglet "Cahier de Facture" (appelé aussi parfois "Chrono") : c'est un état synthétique listant la liste des factures émises. Cela est pratique et utile à plusieurs choses :
* vérifier d'un simple regard que toutes les factures (sans exception) ont bien été comptabilisées.
* s'assurer aisément de ne pas avoir de doublons au niveau de la numérotation des factures
* ...

Cf. ci-joint

-> j'ai rajouté un bouton "CONSULTER les factures". Ta macro balayera l'ensemble des onglets, et affichera toutes les feuilles dont le nom commence par le mot "Facture".

-> tu peux rajouter un bouton "MASQUER les factures" si tu le souhaite (plus tard tu verra que tu peux le faire avec un seul bouton et une seule macro : elle détectera si l'onglet est masqué ou affiché, et en fonction fera l'inverse : afficher ou masquer.

Pour ton problème d'onglet "Clients" et "produits" masqué et bloqué, regarde le fichier ci-joint, c'est débloqué

Pour que tu puisse les voir, j'ai temporairement désactivé ta macro qui se déclenche à l'ouverture.

Bonne continuation.

 

[sokalbr]

Re : Mise à jour des stocks après facturation

Bonjour Excel-lent,

C'est vraiment très interessant ce que tu me proposes de faire ! Je pense que là il n'y as plus rien à rajouter (j'ai peur que ça en fasse de trop après).
Je vais m'occuper des derniers boutons/macro à faire, mais par contre j'ai toujours le soucis des feuilles clients et produits qui sont bloquées (je ne peux pas modifier les cellules alors que pour la facture tout marche) et c'est plutôt embêtant !

En tout cas, je tiens encore une fois à te remercier pour tout ce que tu as fait pour moi.
Je te tiendrai au courant de ma note de projet, que je passe le 28 mai 2012 .

Bien à toi,
Valentin

Edit : je te reposterai une fois le travail fini

Edit 2 : GÉNIAL !!! j'ai réussi à débloquer mes deux feuilles grâce à une petite macro trouvée sur internet qui débloque tout c'est vraiment très pratique !

 

[sokalbr]

Re : Mise à jour des stocks après facturation

Re Excel-lent,

Voici ci joint le projet, j'ai réussi à faire :

- en cliquant sur bouton : duplication onglet "Modèle facture", Renomme (en "Facture + numérotation chronologique"), Coloration de l'onglet (orange), réinitialise "Modèle facture", supprime le bouton de la nouvelle facture

- vu que j'ai pu débloquer mes feuilles "Produit" et "Client" j'ai mis en place une liste de choix déroulante pour les références produit dans la facture

- je laisse tomber le cahier des charges ça en fait de trop et aussi trop compliqué (malgré le fait que ça soit très pratique)

- consulter/masquer factures : impossible d'y arriver j'ai mis un brouillon de code que je ne comprends même pas totalement qui ne marche pas donc si jamais tu pouvais m'éclairer à ce sujet ça serait sympa

Voilà dis moi ce que tu en penses



EDIT : je viens de réparer un bug sur la suppression du bouton

 

[sokalbr]

Re : Mise à jour des stocks après facturation

Bonjour tout le monde, bonjour Excel-lent,

Voici une version finale de mon projet, j'aimerai avoir avis et conseils quand au codage, voir si il y a des bugs me les signaler.

A savoir, les consignes du projet :
- un escompte de règlement est appliqué pour les paiements au comptant,
- une remise est appliquée à différents taux à partir de 2500, puis de 5000€ d'achat,
- seules les cases en jaune sur la facture peuvent être saisies,

Le but étant d'automatiser la facturation pour une petite PME.

En vous remerciant d'avance,

Valentin

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Bonsoir Valentin,

J'ai testé/regardé ton fichier : chapeau bas!

Les 2-3 derniers points, je regarderais demain sur Excel 2007-2010 (une partie de ton code ne fonctionne pas sous Excel 2003 - mais ce n'est pas grave. Comme tu utilise la version 2007-2010, l'important est qu'il fonctionne sur CETTE version.

Petite remarque :
L'astuce que tu as utilisé pour nommer ta liste de clients (cellules nommées - taille adaptable en hauteur automatiquement), il faudrait faire de même pour ta liste "produit" nommé : "REFPRODUIT"

Une fois fait, utiliser ce nom pour tes formules sur ton modèle Facture dans les zones :
-> C14:C23
-> E14:E23

En tout cas, bravo pour ton code :
-> tu as pensé à mettre des commentaires sur les points importants/complexes
-> tu as bien pensé à utiliser à bonne escient les sauts de lignes, retrait, commentaires pour rendre ton code plus clair et plus compréhensible.

Ensuite, il te reste plus qu'à relire l'ensemble de ton code et t'assurer que tu comprend TOUT, pour pouvoir répondre aux éventuelles questions des examinateurs. Sinon n'hésite surtout pas à poser des questions pour te faire expliquer telle ou telle point.

Après cela (optionnel), s'il te reste du temps et que tu veux continuer à progresser, nous pourrons te donner quelques astuces pour raccourcir ton code (en longueurs et temps d’exécution).

Bonne soirée

 

[Excel-lent]

Re : Mise à jour des stocks après facturation

Salut Valentin,

Testé sur Excel 2010, comme prévu tout baigne

A te lire

Cordialement

 

[Regueiro]

Re : Mise à jour des stocks après facturation

Bonsoir à Tous
Très belle application en effet.
Petit plus lorsque tu ouvres par exemple "modifier / Supprimer .. Clients
Ta feuille reste ouverte.
Tu peux rajouter ce code dans la feuille Clients et pour les autres feuilles aussi en modifiant ("...")

Private Sub Worksheet_Deactivate()
Sheets("Clients").Visible = xlSheetVeryHidden
End Sub

Ainsi lorsque tu retournes dans ta feuille Accueil la feuille Clients se masque à nouveau
Bonne soirée
A+