Aide VBA

Bonjour,

Principe du fichier :

L'utilisateur choisit l'aliment dans la liste déroulante du tableau et saisi la quantité, ce qui détermine les valeurs correspondantes pour les glucides, lipides, protéines et calories. Le total (somme de ces valeurs) est ensuite copié/collé plus bas (afin de cacher le tableau à l'utilisateur). Enfin, le graphique compare les dernières valeurs saisies avec les nouvelles afin de faire apparaître une tendance sur le graphique (un visuel des modifications).

=> Dans ce fichier, je souhaiterai que l'utilisateur puisse ajouter ou supprimer des lignes du tableau sans que cela n'ait d'impact sur le reste.

J'ai fais un test mais ça ne fonctionne pas, pouvez-vous m'aider svp ?

Le problème vient de la macro mais aussi du graphique car il va chercher le total du tableau (graphique bleu) et comme on ajoute ou retire des lignes il ne faut plus que ça soit des cellules fixes.

Fichier :

Merci.

Bonjour,

Pourquoi avoir envoyé la ligne des totaux vers la ligne 10000 ?

Pour éviter qu'il ne remonte trop vite au fil des suppressions ?

On peut penser que le petit tableau en haut ne dépassera jamais une trentaine de lignes ?

A+

Bonjour,

Simplement pour cacher la ligne et éviter de tomber dessus. C'est pas spécialement très important mais j'aurais pû mettre moins bas en effet. Ça allégerait le fichier.

Bonjour,

Je travaille sur le sujet, mais d'une manière assez éloignée de la question initiale.

Ce qui prendra un peu de temps...

Si d'autres veulent se pencher sur la question proprement dite, ne vous privez pas !

A+

Bonjour

pensons Excel et ses fonctions de base avant de penser VBA. N'est-ce pas galopin01 ?

alors il faut juste:

• liste dérolante pour la saisie des produits en colonne B
• RECHERCHEV pour afficher les valeurs lues dans la base de données (cal, glucides etc.)

ne pas mettre les totaux en bas de colonne (car il devient difficile de comprendre comment ajouter une ligne). Mettre les totaux en haut de la feuille

masquer la base, verrouiller les cellules avec formules

conseil, mettre du grisé dans les cellules que l'utilisateur ne doit pas cliquer, en plus du verrrouillage. Confort d'utilisation.

c'est tout

pas de VBA, juste des + et des *

amitiés

Bonjour à vous deux,

Déjà merci pour votre retour.

galopin01, peux-tu m'en dire plus sur ton idée ? Je suis intéressé d'avoir ton retour/tes conseils.

jmd, en effet, mettre le total au dessus n'est pas bête, c'est en tout cas une solution.

Merci également pour tes conseils, je vais mettre tout ça en application.

Je reste à l'écoute de toutes nouvelles solutions.

bonjour,

Désolé je n'avance pas vite en ce moment : C'est peut-être un peu lié à la canicule !

Vu mon age et mon état de santé tout est plus compliqué... Pour l'instant je suis en mode ECO !

De plus je me suis laissé un peu piéger par ton projet : Il y des situations ou un tableau Excel n'est pas du tout un avantage, du coup comme tu avais commencé avec je me suis embarqué dans cette impasse.

Je vais recommencer en convertissant le tableau en plage : Ce sera beaucoup plus simple.

Je ne t'en dis pas plus. Mais j'ai bien compris ton projet et je te ferai un retour dès que ça aura pris forme... Encore une fois désolé si ça n'avance pas aussi vite que tu le voudrais !

A+

re à tous

galopin01

il faut TOUJOURS mettre les données sous forme de Tableau

ça fiabilise toute la suite d'un projet

et ça la simplifie

c'est une fonction de base d'Excel

selon toute logique, elle devrait figurer au menu Données, mais elle est tellement importante qu'on l'a mise au menu n°1

dès lors, je l'utilise en permanence

Excel aussi !

charge donc des données issues de Power Query pour voir

vite : refais-toi une nouvelle idée sur les Tableaux

bonne santé

amitiés

bonjour,

Bon... Je te donne un aperçu des mes cogitations :

Je ne te dis pas que c'est fini, mais c'est pour le moins compréhensible. S'il faut corriger des bricoles je saurai faire.

Mais c'est inutile que je me fatigue si ce que j'ai fait ne t'intéresse pas...

Sur la finalité du truc YA rien de changé.

Sur le chemin utilisé : Tous les chemins mènent à Rome... Hein !

Comme je t'avais dis le tableau Excel utilisé en Feuille2 mène dans une impasse. Je l'ai donc converti en plage ordinaire.

J'ai aussi un peu modifié ta BD qui sert de référentiel : Je n'utilise que la colonne 3 ou j'ai comblé les vides qui font comme un trou peu pratique.

J'ai aussi modifié la première colonne que j'ai remplacée par un index qui m'est beaucoup plus utile.

Cette BD est triée sur la colonne C

Si tu retries sur la colonne 1, tu retrouveras ta BD d'origine... Mais par la suite le programme la retrie systématiquement sur cette colonne C : C'est quand m^me beaucoup plus pratique pour y retrouver ses petits...

Sur la BD YA beaucoup de trucs à dire car tes données étaient pour moi inutilisables, j'ai donc transformé toutes les virgules en point (car je travaille en point décimal) et tes virgules étaient mal interprétées par mon système. Par suite comme je n'ai pas fait dans le détail toutes les virgules des colonnes 2 et 3 ont été transformées en point également, ce qui donne des libellés d'aliments un poil modifiés... Mais ce sont de menus détails facilement modifiables...

Cet index est aussi utilisé dans la deuxième feuille.

YAKE 2 boutons 1 pour vider le tableau, 1 autre pour mémoriser la compo actuelle

En cliquant (*) sur la première colonne on fait apparaitre un combo qui permet de choisir un aliment :

Il suffit d'entrer les premières lettres : ESCA renvoie immanquablement à escargot ensuite YAKA choisir dans la liste si tu veux des petits gris ou des gros bourgogne...

Ensuite tu peux rentrer (ou pas) une quantité, si tu valides directement c'est 100 qui s'inscrit sur ta feuille...

Ensuite tu peux modifier manuellement la quantité sur la feuille...

Les sous totaux sont cachés derrière le graphique et le graphique à été mis en mode "échelle logarithmique" mébon là encore c'est une convenance personnelle facile à changer...

(*) En cliquant sur cette première colonne le formulaire s'ouvre sur la ligne cliquée Ce n'est pas évident car le formulaire est vide. Mais si tu cliques sur "Supprimer" c'est bien cette ligne qui est supprimée. Et si tu remplaces tes escargots par du homard la ligne est bien modifiée.

Pour les dernières lignes vides quelque soit la ligne cliquée c'est la première ligne vide qui est remplie.

J'ai aussi flinguée la liaison qui me les brisait menu...

Bon je te laisse découvrir et jouer un peu avec le bébé : Immanquablement tu vas essuyer les plâtres et découvrir quelques bugs, mébon... L'essentiel est de voir si ça peut t'être utile !

A+

Bonjour à vous deux !

Merci pour vos conseils !

Merci galopin01 pour ton retour, tu as l'air d'avoir fait un gros travail sur le fichier et je t'en remercie. Je suis pas disponible pour l'instant (je suis juste sur téléphone) mais ça donne envie ! Je me presse de regarder tout ça dès que possible et te fais un retour.

galopin01, je viens de tester le fichier, en un mot : impressionnant.

Je suis incapable de faire tout ça, je me rend davantage compte des possibilités qu'offre Excel et constate qu'il me reste énormément à apprendre...

Le fichier correspond parfaitement à mes attentes, il répond à plusieurs de mes interrogations à la fois, bref merci !

Quelques points que j'aimerai comprendre :

• La 1 ère ligne de code "option explicit", concrètement ça sert à quoi ?
• Je comprends pas tout le code mais déjà pour moi le "TOC" c'est pas clair, pourrais-tu me dire à quoi ça correspond stp ?
• La dernière ligne de code VBA correspond au blocage du clic droit c'est bien ça ?
• Comment tu vas chercher les valeurs nutritionnelles sans rechercheV ? Grâce à l'insert form ?
• Merci pour le graphique logarithmique, je cherchais justement le moyens de faire ça

J'ai également noté quelques points que je souhaiterai modifier, j'ai pas encore fait beaucoup de recherche sur la façon de procéder je dois l'avouer :

- Mettre en évidence les cellules à sélectionner pour compléter le programme (colonne ID). -> existe-t-il un moyen "design" autre que de mettre une couleur de fond sur les cellules ?

- Petit détail, si on clique sur les cellules de la colonne ID, A2 par exemple, et qu'on choisit de cliquer dans la "boite de dialogue" sur "Annuler" puis qu'on re-sélectionne la même cellule (A2 dans le cas présent), la boite de dialogue n’apparaît pas. -> peut on dire à Excel de re-sélectionner la cellule A1 par exemple, juste après avoir fait "annuler" dans la boite de dialogue afin de pouvoir sélectionner A2 et avoir la fenêtre de choix ? comme on le ferait avec Range("A1").Select en VBA.

- Supprimer la notion d’eau dans la liste déroulante -> quand on choisi un aliment dans la liste déroulante on voit les valeurs nutritionnelles (calories, protéines, etc...) et il y a également la quantité d'eau qui s'affiche, l'as tu mise pour les alcools ?

- Un détail, quand on supprime une ligne (grâce à la boite de dialogue) la bordure inférieure de la ligne supprimée disparaît. -> peut-on corriger ce point ?

- L'utilisateur peut supprimer une ligne avec le menu excel « Supprimer les cellules » -> est-il possible de bloquer l'utilisation de ce menu ? Simplement en mettant une protection sur la feuille peut être ?

- Changer le nom de la boite de dialogue et les titres -> j'ai trouvé après avoir noté les points à modifier

- La touche de verrouillage du pavé numérique qui se débloque ? -> Il me semble que la touche Num lock se déverrouille, peut être dû à une ligne de code, sais-tu comment modifier ce problème ?

Sinon rien à redire, merci pour le temps consacré, je suis ravi de voir qu'il existe encore des gens prêt à aider et donner de leur temps.

Bonsoir,

Option Explicit oblige à déclarer explicitement toutes les variables utilisées.

Le ToC est l'ensemble de la plage ligne d'en-tête + saisies effectuées tels que déclaré dans le Gestionnaire de nom.

Cette plage comme toutes autres déclarées dans le Gestionnaire de Nom est dynamique. C'est à dire qu'elle s'adapte exactement au tableau existant. (A condition de ne mettre aucune autres données lignes 1 et aucunes autres données colonnes A à G.

Oui le clic droit a été inhibé pour éviter des suppressions sauvages.

Les suppressions ne doivent se faire que par les boutons RAZ pour effacer la totalité des lignes.

Le bouton supprimer du UserForm pour supprimer une seule ligne (la ligne en cours)

Quand on supprime une ligne, on la supprime ! (Et les autres remontent) donc puisqu'il n'y a plus de lignes il n'y a plus de bordure dans le Toc qui diminue de ce fait d'une ligne...

La ligne se rajoute à chaque ajout...

Les données affichées ou utilisées sont celles de la base de donnée (BD) colonnes 1, 3 5 ,7 ,8 , 9.

Ce qui résoud en même temps les problèmes d'eau et d'alcool : si tu n'en veux plus tu les supprimes de la BD...

La récupération des données sélectionnées se fait dans la procédure Private Sub cboRech_Change() par la création d'un outil un peu complexe : le Dictionnary

Le Dictionnary est un instrument qui répertorie à la manière d'un dictionnaire les données utilisées dans la feuille BD

J'ai stocké ces données dans des TextBox (invisibles) pour les réutiliser ensuite, mais c'est juste un résidu de mes cogitations sulfureuses après moultes turpitudes due à ton Tableau que j'avais tout d'abord bêtement voulu garder.

Mais en réalité on pourrait supprimer tous ces Textbox et transférer directement les valeurs du formulaire sur la feuille Excel. Comme ça ne présentait qu'un intérêt mineur, j'ai laissé en l'état. L'utilisation du Dictionnary n'étant pas intuitive pour un débutant, ces TextBox sont malgré tout une commodité.

Les valeurs calculées des Calories le sont grâce à une formule du type RECHERCHEV en fonction de la quantité saisie colonne C : Pour la simplicité d'utilisation cette formule est gérée par le Gestionnaire de Nom (SLFRM)

Pour envoyer la sélection en A1 après annulation modifier la macro de la manière suivante :

Private Sub cmdDelete_Click()
   With WsP
      .Range(.Cells(iRow, 1), .Cells(iRow, 7)).Delete shift:=xlUp
      . [A1].Activate    'ligne à rajouter (attention au point avant [A1])
   End With
End Sub

Il n'y a pas d'incidence du code sur [NumLock]

Je te déconseille de colorer par quelque moyen que ce soit la colonne 1 (et surtout pas avec MFC)

Je te déconseille également de chercher à inhiber le menu supprimer d'Excel : Sauf à dire que ce n'est pas toi qui te servira du classeur ça ne me parait pas bien compliqué de se rappeler qu'il ne faut pas supprimer de lignes.

En tout cas je ne m'engagerai pas dans cette voie là avec un débutant.

A+

... Par contre ce qu'on peut faire très facilement , c'est faire en sorte que le Userform soit appelé également par un clic sur la colonne 2

Comme de toute façon la colonne 2 n'est pas éditable (on ne peut pas modifier le libellé sous peine de rendre le RECHERCHEV inopéprant) ça peut être une bonne manière de protéger l'accès en modification. Par contre inévitablement si on affiche aussi le UserForm sur clic dans la colonne 2, inévitablement tu vas avoir des affichages non désirés...

C'est à toi de voir : Pour modifier l'accès au UserForm et activer l'accès par clic sur les colonnes 1 ou 2, dans la Private Sub Worksheet_SelectionChange modifier la ligne :

      If iC = 1 Then         'ligne à remplacer par:
      If iC < 3 Then

A+

Bonjour galopin01,

Merci pour tes précisions et nombreux conseils que je vais suivre et tenter de mettre en place dès aujourd'hui.

Un dernier point, je souhaite partager le fichier avec des personnes qui n'ont pas Excel, connais-tu une façon de procéder ? Car je doute qu'excel viewer et google sheet permettent de faire tourner le fichier et de pouvoir apporter des modifications (même de simples saisies)...

D'avance merci !

Je viens de me rendre compte d'un autre problème, lorsqu'on modifie la quantité, le nombre de calories se modifie en revanche les protéines, glucides, lipides restent identiques or ces valeurs devraient bouger selon la quantité saisie.

Pourrais-tu m'aider sur ce point ? Car j'ai tenté de régler le problème en m'inspirant de la formule que tu as mise dans le gestionnaire des noms SLFRM mais ça n'a pas fonctionné

Je ne vois guère de moyen de mettre cela en place pour des gens qui n'ont pas Excel. Mais je ne suis pas un spécialiste...

Pour les 3 autres colonnes il m'avait semblé que t'on fichier renvoyait des nombres fixes...

Si tu veux mettre en place un RECHERCHEV pour ces colonnes également procéder comme suit :

Sélectionner la cellule E2

Aller dans le Gestionnaire de nom et créer un nouveau Nom : SLFRMX

dans la zone Fait Référence à coller cette formule :

=RECHERCHEV('P1'!$B2;BDA;COLONNE();FAUX)/100*'P1'!$C2 puis valider.

Dans la cellule E2 entrer la formule suivante :

=SFRMX

...ensuite étendre cette formule vers la droite colonne F et G puis vers le bas pour toutes les cellules ou tu as des nombres.

Dans VBA Modifier la Sub WriteRecord :

Private Sub WriteRecord()
   Cells(iRow, 1) = Val(Me.tb0) 'iD)
   Cells(iRow, 2) = Me.cboRech
   Cells(iRow, 3) = Val(Me.tb5) 'Qte
   Cells(iRow, 4).Formula = "=SLFRM"
   Cells(iRow, 5).Formula = "=SLFRMX"
   Cells(iRow, 6).Formula = "=SLFRMX"
   Cells(iRow, 7).Formula = "=SLFRMX"
   With WsP 'la suite sans changement

C'est tout.

A+

Pendant que je te tiens, je peux te poser une question ?

J'ai été un peu interloqué par certaines données : Quelle est l'origine de ces données et leur fiabilité supposé ?

Je parle simplement de la valeur en Calories.

Pourquoi certaines valeurs sont-elles à 0 contre toute toute attente alors qu'une autre pratiquement identique affiche 200 ou plus...

Quelle crédit peut-on accorder aux données de produits qui affichent une valeur zéro en calories ?

J'ai ainsi supprimé la ligne 269 du document d'origine qui affichait :

Sandwich (aliment moyen) à 0 alors qu'aucun autre n'est inférieur à 200...

Mais il y a aussi beaucoup d'autres exemples qui m'interrogent énormément. (D'autant que beaucoup d'autres paramètres invérifiable à mon niveau sont documentés)

J'ai pas vraiment cherché sur Google à vérifier ces chiffres, mais je serais curieux que tu me déniaises un peu. Et si en plus tu me donnes une BD avec des garanties de fiabilité, là ce serait le graal !

A+

bonjour à vous

salut galopin01 au passage

solution pour les personnes sans Excel : OpenOffice gratuit

détail : abandonner tout VBA. La solution que j'ai décrite plus haut passe sur Excel et sur OpenOffice,

si besoin de faire un exemple, je reste dispo

amitié à vous

Bonjour,

@galopin01, certaines données sont en effet erronées. J'ai encore un gros travail à faire sur la BD pour la mettre d’équerre. Il s'agit d'une BD en licence ouverte de Ciqual, gérée et validée par l'agence nationale de sécurité sanitaire de l’alimentation, de l’environnement et du travail (ANSES).

@jmd, je n'ai pas oublié ton conseil, je travaille dessus également au cas où je rencontrerai des problèmes de partage du fichier.

Je dois avouer que je n'avance pas vite, faut dire que la chaleur y est pour beaucoup...

Je dois avouer que je n'avance pas vite, faut dire que la chaleur y est pour beaucoup...

Sur que ça ne motive pas des masse pour rester derrière son écran !

Oui. elle gagnerait à éliminer pas mal de redondants et non caractéristiques (Je sais pas si beaucoup de personnes mangent des petits pois ou des pommes de terre crues...)

Enfin il m'a fallu environ 1 minute pour pour compléter la colonne calories qui semble quand même essentielle dans ce contexte. Je ne te ferai pas l'insulte de te fournir la formule !

Ce qui sera sans doute beaucoup plus difficile à documenter ce sont quelques spécialités absentes mais pourtant très courantes telles que "Boursin" et bien d'autres plats cuisinés dont la fourchette de valeurs varie parfois du simple au double selon le distributeur...

A+