Ventilation d'un tableau de données en plusieurs onglets
Bonjour à tous,
Je sais que cette question a déjà été posée mais mon niveau débutant en VBA ne me permet pas de comprendre toutes les nuances des réponses. J'ai un rattrapage VBA à rendre pour dans quelques jours, et pour l'instant bien qu'ayant quasi fini l'exercice sans encombre je butte sur la dernière question demandée: ventiler les données de mon tableau en plusieurs onglets.
En effet, lorsque je clique sur le commandbutton "Ventiler", j'aimerais que plusieurs onglets se créent (un pour chaque client) et que par conséquent, les données de chacun des clients soient récapitulées sur leurs onglets respectifs.
Si une âme charitable pouvait me débloquer, je lui en serait infiniment reconnaissante
Je vous joins mon fichier pour plus de clarté.
Merci,
Amélie
Bonjour,
Ma première impression :
Si on ne vous apprend pas d'abord à indenter le code, et à éviter tout 'Select' inutile, ensuite à mettre des qualificateurs d'objet, à utiliser des boucles, à se servir de la fenêtre propriétés pour doter les contrôles de propriétés par défaut,
la formation est défectueuse, ou bien tu as zappé les explications...
Bonjour MFerrand!
Vous devez avoir raison, on n'a pas du m'apprendre les bases élémentaires ou je n'ai peut être rien écouté du tout en cours. Mais au moins on m'a appris à faire des remarques positives et constructives au lieu de basher gratuitement quelqu'un qui demande de l'aide!
Merci encore pour votre réponse.
Bonne journée à vous.
J'y travaille !
Remarques et explications (au fur et à mesure, pour éviter d'en oublier en route...) :
• Ta liste clients (sur Feuil1) : il est souhaitable de nommer la plage, et de la nommer en dynamique, cela t'évite donc d'avoir à modifier les références, la plage sera toujours couverte par le nom.
J'ai donc nommé Client qui réfère à : =DECALER(Feuil1!$A$1;;;NBVAL(Feuil1!$A:$A))
A noter : c'est la méthode pour nommer une plage dynamique avec la fonction DECALER appliquée sur la première cellule, décalage ligne et colonne nuls, nombre de ligne calculé par le nombre de valeur dans la colonne (quand on a une en-tête en A1 et donc la première cellule de la plage à nommer en A2, on décale à partir de A2 et on ajuste avec NBVAL(........)-1 ; on peut définir un nombre de colonnes s'il y a lieu, éventuellement variable aussi...)
• Contrôles : une chose utile à intégrer quand on débute est de renommer ses contrôles. Il est évidemment inutile de renommer les étiquettes que tu n'utiliseras pas dans le code, mais il est utile de renommer les contrôles que tu as à manipuler pour les doter d'un nom plus explicite qui t'évitera des recherches quand le nombre augmente (et j'ajouterais volontiers, plus court que le nom natif, cela fait moins à écrire, ce qui n'est pas partagé par tous, mais c'est tout de même plus pratique !), et les doter éventuellement d'un numéro d'ordre qui te permettra de les parcourir au moyen d'une boucle.
Il est souhaitable de les préfixer : préfixe de 2 ou 3 lettres indiquant la nature du contrôle (règle à laquelle il convient parfois de contrevenir si tu veux défiler en boucle des contrôles de natures différentes pour récupérer leur propriété par défaut (Value)).
J'ai donc renommé tes contrôles (à voir dans le fichier).
• ComboBox, renommé cbxClient : tu lui affectes la liste source en l'inscrivant dans la fenêtre de propriété, en regard de la propriété RowSource : tu inscris Client (plage nommée). C'est définitif, c'est la valeur par défaut de cette propriété. Elle sera toujours présente à l'ouverture du Userform.
Il est également utile de mettre la propriété MatchRequired à la valeur True. Cela interdit d'inscrire un nom de client ne figurant pas dans la liste dans la ComboBox.
(Un autre dispositif aurait été de le permettre, d'intercepter une valeur de Combo hors liste, de demander confirmation à l'utilisateur pour l'ajouter à la liste et mettre la liste de la Combo à jour... Mais là ça dépasse un peu trop le cadre de ton exercice...)
• Initialize : elle n'avait pour objet que de définir la propriété RowSource, elle est donc sans objet.
Mais dans le même temps tu procédais en fin de validation à un déchargement puis réaffichage du formulaire. C'est une méthode de réinitialisation, assez souvent utilisée à ce que j'ai vu, certes efficace, mais toutefois peu élégante !
Pour réinitialiser tu n'as que 3 choses à faire : redéfinir la valeur de ListIndex de la Combo à -1, effacer la TextBox et mettre les OptionButtons à False (par boucle). Cela peut être fait en masquant le formulaire lors de la validation (après vérif. que celle-ci peut intervenir) et en le réaffichant à la fin de la valid. Tu mets donc les commandes de réinitialisation dans la procédure Activate du Userform.
On pouvait créer une procédure pour cela mais la proc. Activate intervient à chaque affichage du Userform, elle est donc indiquée pour cela.
Au passage, cela te permet d'apprendre la différence entre Initialize et Activate : Initialize intervient au chargement du Userform avant affichage (intervient donc quand tu le charges avec Load sans l'afficher) mais n'intervient plus tant que le Userform n'est pas déchargé (Unload) pour être rechargé. Alors que Activate intervient lors de chaque affichage juste avant.
A suivre...
• L'effacement de ta feuille de saisie : Cells.ClearContents, ça marche bien sûr mais il est également peu élégant de procéder ainsi, cela fait rouleau compresseur pour écraser une mouche !
Ensuite, le Select était inutile (je note qu'il n'y en avait qu'un dans tout ton code, un bon point !
J'ai remplacé par : Worksheets("Feuil2").UsedRange.ClearContents
Le code n'est pas plus long et l'objet est qualifié : quand tu écris Range ou Cells... sans référence à un objet tu as une expression non qualifiée qui donc ne réfère qu'à la feuille active du classeur actif, laquelle n'est jamais assurée de stabilité. Si ton code prend de l'ampleur tu peux te retrouver dans des conditions où la feuille active n'est plus celle que tu croyais, et il est alors un peu tard pour reprendre tout et le modifier (surtout long après coup).
Conseil : mettre un qualificateur et utiliser l'instruction With... End With pour ne pas avoir à le répéter.
(NB- Cette instruction ne permet pas seulement d'éviter la répétition quand on code : VBA met l'objet indiqué ainsi en mémoire, n'a donc plus à le rechercher et cela rend l'exécution plus rapide).
• L'initialisation de la feuille de saisie : tu y procédais lors de la validation, en insérant une ligne (en 2), remettant l'en-tête en ligne 1 et réaffectant un format de cellule sur une partie de la ligne 2. Donc à chaque validation ! Une fois suffit !
Il convient donc lors de la validation de vérifier si la cellule A1 est vide et si c'est le cas lancer une procédure qui ne sera alors exécutée qu'une seule fois :
Sub InitFeuilSaisie()
Dim ett
ett = Split("Client;Couleur;Quantité;Total HT;Remise;Sous-Total;TVA;Net à payer", ";")
With Worksheets("Feuil2")
.Range("A1").Resize(, 8).Value = ett
.Range("A1:H1").HorizontalAlignment = xlCenter
.Range("A2:C2").HorizontalAlignment = xlCenter
.Range("D2:H2").NumberFormat = "#,##0.00 $"
End With
End SubJe l'indique en entier car les explications vaudront pour d'autres cas ensuite. On évite les énumérations fastidieuses cellule par cellule, VBA offre quantité de moyens de faire autrement.
Split est une fonction qui permet de transformer une chaîne en tableau en utilisant un élément séparateur inclus dans la chaîne, ici un ";". ett est une variable de type Variant à laquelle on affecte un tableau des éléments de la chaîne séparés par un point-virgule, donc 8 éléments (indicés de 0 à 7)
Array est une autre fonction qui permet également de constituer un tableau en séparant chaque élément par une virgule (séparateur VBA). En utilisant Split à la place d'Array pour des valeurs texte, on s'économise une masse de guillemets...
Les tableaux étant d'utilisation particulièrement fréquente en VBA, ces deux fonctions sont à connaître absolument.
On place donc l'en-tête en affectant l'ensemble de la ligne...
A noter : en manuel dans Excel quand on a des valeurs à affecter, on ne peut que soit les taper, soit les copier et coller... En VBA on peut faire autrement :
on fait : plage1.Value = plage.Value, on affecte ainsi globalement les valeurs d'une plage à une autre de taille identique.
ici on a fait : plage.Value = tablo
A cet égard, noter qu'un tableau unidimensionnel peut être affecté sur une ligne. Pour l'affecter sur une colonne, il aurait fallu utiliser la fonction Transpose.
(Dans un tableau à 2 dimensions, la 1re constitue les lignes et la 2e les colonnes.)
Le format de cellule est aussi affecté globalement à la plage concernée, le format étant le même.
Au passage, j'ai centré les titres et les éléments non numériques de la 2e ligne.
On pourrait également modifier la police des titres, etc.
On ne fait cette initialisation qu'une fois.
Procédant par la suite à une insertion de ligne en position 2 (ce qui était une bonne idée
.Range("A2:H2").Insert xlShiftDown, xlFormatFromRightOrBelowfaisant ainsi en sorte que le format de la ligne insérée s'aligne sur celle d'en-dessous.
(NB- Par défaut, le format s'aligne sur ligne au-dessus, la paramètre de format permet d'inverser...)
• Contrôle TextBox (renommé tbQuant) : on attend un nombre, il convient donc au minimum de s'assurer que la valeur saisie est numérique et l'effacer quand elle ne l'est pas. La procédure Change permet ce premier contrôle de saisie (on peut le faire autrement mais au cas particulier cette vérification suffit).
• Absence de bouton Annuler ou Fermer : il généralement indispensable de pouvoir sortir d'un formulaire sans rien faire (et sans être obligé d'utiliser la croix de fermeture). De plus ici, le Userform étant conçu pour faire des saisies enchaînées, il faut qu'on puisse fermer à la fin ! J'ai donc ajouté un bouton Fermer.
La suite est à voir dans la procédure de validation.
La validation implique 3 phases :
1) s'assurer qu'on peut donner cours à la validation, soit que toutes les données à saisir pour ce faire l'ont été et sont valides,
2) récupérer les valeurs à affecter en calculant celles qui ne résultent pas de la saisie,
3) affecter les valeurs.
Les phases 2 et 3 peuvent être confondus, lorsque l'on affecte directement les valeurs de contrôle ou les valeurs calculées aux cellules concernées.
Si elles peuvent ne pas l'être, on a toujours intérêt à le faire : moins on a d'interaction avec les objets Excel, plus l'exécution du code sera rapide.
On a ici une ligne de 8 cellules à servir, dont 3 résultent de la saisie et les autres de calculs. On peut composer la ligne de valeurs à affecter dans un tableau et l'affecter à la fin..
Pour la partie vérification, il faut vérifier que les 3 saisies ont été effectuées :
- si cbxClient.ListIndex = -1, aucune sélection client n'a été faite, on le signale et redonne le focus au Combo ;
- si tbQuant ="", on a omis la quantité, on le signale et redonne le focus à la TextBox ;
- si aucun OptionButton opbC n'a été coché, on le signale également.
Dans le cas des couleurs (OptionButtons), on parcourt les valeurs de opbC1 à 6 avec une boucle, si l'un a la valeur True (il ne peut y en avoir qu'un) on sort de la boucle. la variable de boucle clr aura alors une valeur de 1 à 6 qui permettra de déduire la couleur choisie. (Si aucun coché, la variable aura la valeur 7, qui permet de savoir qu'il faut inviter l'utilisateur à saisir la couleur...)
A l'issue des vérifications on peut masquer le Userform, ce qui permettra à l'utilisateur de voir le résultat apparaître dans la feuille.
Il faut préparer les résultats à affecter :
On crée un tableau des couleurs (tclr) de la même façon que pour les en-tête de la feuille, mais en incluant un premier élément (élément 0) vide (il suffit de commencer la chaîne à splitter par un séparateur), de façon que l'indice du tableau soit en correspondance avec les numéros d'ordre des boutons et que la variable clr soit donc directement l'indice dans le tableau de la couleur choisie.
On crée également 2 tableaux pour évaluer plus rapidement les remises : un pour les quantité (500, 300, 100, 0) avec Array et un autre pour les taux de remise correspondantes. En parcourant le premier tableau pour savoir à quel élément du tableau la quantité demandée est supérieure ou égale, on obtiendra l'indice du taux de remise à appliquer dans le second tableau.
Le prix unitaire hors taxe et le taux de TVA sont mis en constantes en tête de procédure, avec les déclarations de variables.
Il aurait été bien aussi de mettre les éléments de remise en constantes mais leur utilisation sous forme de tableau ne le permet pas.
(Et la création de types de données personnalisées nous aurait entraînés trop loin...
L'utilisation de constantes à l'avantage qu'elles sont mémorisées par VBA et que lors de modification on n'a que les constantes à modifier.
Une autre organisation (plus performante, tu peux modifier en ce sens si tu penses que c'est utile de le faire valoir) est de mettre les 2 constantes précitées (puht et ttva) en constantes au niveau module, de déclarer les variables (Variant) utilisées pour les tableaux de remises (qrem et trem) également au niveau module. et d'affecter les valeurs de tableaux (Array) à ces variables par la procédure Initialize. Elles seraient affectées une seule fois pour toute la session et la procédure de validation qui les utilise en serait allégée et donc plus rapide...
Revenons à la validation : on va opérer par la création d'un tableau (encore un !) déclaré en tant que tableau [CCli(7)] de type Variant, accueillant tous types de données, à 8 éléments (0 à 7)
On affecte à l'élément 0 : le nom client, à partir de la liste source (la ligne est donnée par la valeur ListIndex + 1 de la Combo).
[L'affectation de la valeur de la Combo convenait aussi, mais si l'on a une base clients avec d'autres éléments à prélever, sans avoir besoin de les faire apparaître dans le Userform, on peut plus facilement le faire ainsi, éventuellement avec une boucle.]
Elément 1 : la couleur = tclr(clr), comme on l'a vu.
Elément 2 : la quantité = CInt(tbQuant.Value) ; on convertit en Integer, ce qui élimine tout risque de saisie d'un nombre décimal (et les quantités remisables ne permettent pas de penser qu'on attend des commandes supérieures à 32000 ! donc inutile de convertir en Long).
Elément 3 : prix HT, c'est l'élément 2 *puht
Pour l'élément 4 on parcours le tableau qrem, on sort de la boucle quand l'élément 2 est supérieur ou égal à un élément du tableau.
Il est égal à l'élément 3 *trem(i), i étant la variable de boucle sur qrem qui permet le choix du taux en fonction de la quantité...
L'élément 5 n'est que la soustraction de l'élément 4 à l'élément 3.
L'élément 6 n'est que ce dernier élément *ttva, et l'élément 7 la somme des deux précédents.
On a donc ainsi fait la totalité des calculs sans aucune interaction avec la feuille. Il s'agit maintenant d'affecter le résultat :
on insère donc une ligne en position 2 et on affecte :
.Range("A2:H2").Value = CCliEt le Userform réapparaît, réinitialisé.
Le fichier, auquel il manque encore la ventilation, problème que tu as posé
Cela va venir mais je dois manger d'abord !
Il s'agit de :
- vérifier que la feuille Client existe,
- si elle n'existe pas la créer,
- y insérer les valeurs issues de la saisie.
A tout à l'heure.
Là, on va aller au plus simple. Copier et coller dans la mesure on colle du même coup les valeurs et les formats...
Le bouton Ventiler lance une procédure qui définit la ligne de fin sur la feuille (méthode commune par End(xlUp)), parcours les lignes à partir de la 2, pour chaque ligne prélève le nom du client en A, teste au moyen d'une fonction (FCliExist) si la feuille client fait défaut et dans ce cas lance une procédure InitFCli (qui ajoute une feuille, la nomme du nom client et copie la ligne d'en-tête de Feuil2 pour l'y coller en première ligne), puis lance une procédure de copie ComCli en lui passant le numéro de ligne à copier (qui copie et colle).
L'ajout d'une feuille entrainant automatiquement l'activation de la nouvelle feuille, les opérations de InitFCli qui se terminent par la réactivation de Feuil 2 sont inhibées à l'affichage de façon que ce mouvement reste invisible à l'utilisateur.
A défaut d'indications plus précises de ta part, on procède à la ventilation de haut en bas de la feuille et on inscrit aussi sur les feuilles cibles dans le sens descendant : la méthode de saisie mettant la saisie la plus récente en première position, de fait on rétablit l'ordre chronologique sur les feuilles cibles...
Mais rien n'empêche si tu le souhaites de procéder également à une insertion de même type sur les feuilles cibles et à un transfert de valeurs plutôt qu'un copier-coller (l'initialisation doit alors procéder à la mise en place du format...)
Du fait que tu as prévu un effacement à partir du Userform, je n'ai pas prévu d'effacer Feuil2, mais j'ai introduit une mention "ok" en colonne I de la ligne copiée, et un test qu'il n'y ait rien dans I avant de copier, de façon à ne pas pouvoir servir les feuilles cibles avec les mêmes données à répétition. Tu peux réadapter l'ensemble selon ce que tu souhaites obtenir au final...
Pour tester si la feuille existe, la fonction opère une commande sur cette feuille, commande mise sous gestion d'erreur, si une erreur se produit, le gestionnaire renvoie à une étiquette d'erreur et la fonction renvoie FAUX, si la commande aboutit, elle renvoie VRAI.
J'ai retenu comme commande de test le fait de rendre la feuille visible, ce qui permet en outre que si cette feuille existe et est masquée, elle est simultanément rendue visible...
Cordialement.
J'ai essayé de t'amener un certain nombre d'éléments parmi ceux qui me paraissent indispensable à une bonne utilisation de VBA, en essayant de les diversifier dans le cadre de l'exercice, sans toutefois les compliquer trop (par d'autres notions qui ont le temps avant de venir au premier plan). J'ai fourni des explications sur tous les éléments utilisés, en essayant d'être succinct (on ne rit pas !!!) autant que possible...
Si tu penses que cela manque d'aspects positifs et constructifs, je reste à ta disposition pour tout complément utile ou nécessaire...
Si je me permets un avis tranché (disons-le ainsi !) au départ, qui n'a pas eu l'heur de te plaire, mais le contraire m'aurait fort surpris
Dis-toi aussi que fournir des explications sur du code c'est le double de travail (et parfois plus) que de l'écrire...
Habituellement, je laisse passer un délai avant d'en arriver à l'aspect "positif" : tu bénéficies du fait que je suis encore un peu sous les effets d'une anesthésie et que cet exercice a eu pour rôle de remettre mes neurones en bon ordre de marche... (pas de remerciements donc ! J'ai déjà récolté le bénéfice...
Cordialement.
Woah MFerrand!
Je ne sais pas comment vous remercier. Désolée d'être partie au quart de tour mais apparemment vous vous y attendiez!
Je vous remercie surtout du temps que vous avez consacré à me donner toutes les explications nécessaires. Cela ne saute pas aux yeux, mais VBA m'intéresse vraiment et grâce à vous je vais pouvoir améliorer mes bases jusqu'ici assez fragiles.
Encore merci et bonne journée à vous!
Mais ne me remercie pas !
A une prochaine fois, Amélie.