Aide pour simplifier une Macro

Bonjour à tous,

J'ai creer un moteur de recherche avec un liste déroulante.

En fonction de la valeur selectionnée dans cette liste déroulante, je souhaite masquer toutes les cellules sauf la plage qui correspond au tableau de la valeur selectionnée.

J'y arrive tres bien avec la macro ci dessous mais cela prend 2min a chaque fois car excel passe en revue toutes les occurences avant d'appliquer la macro ...

Je souhaiterai simplifier la macro pour remedier à ce prombleme . peut etre avec i= ?

Merci d'avance

Voici la macro :

With Rows("6:27")

If Range("R4") = Sheets("Compétences").Range("D53") Then

.Hidden = False

Else: .Hidden = True

End If

End With

With Rows("28:49")

If Range("R4") = Sheets("Compétences").Range("D54") Then

.Hidden = False

Else: .Hidden = True

End If

End With

With Rows("50:71")

If Range("R4") = Sheets("Compétences").Range("D55") Then

.Hidden = False

Else: .Hidden = True

End If

End With

With Rows("72:93")

If Range("R4") = Sheets("Compétences").Range("D57") Then

.Hidden = False

Else: .Hidden = True

End If

End With

With Rows("94:115")

If Range("R4") = Sheets("Compétences").Range("D58") Then

.Hidden = False

Else: .Hidden = True

End If

End With

etc etc etc

Bonjour,

2 minutes pour exécuter ce bout de code, tu caricatures un tantinet ... ou alors ta machine devient un peu poussive ... ou encore ce bout de code fait partie d'un ensemble plus vaste et l'ensemble en question tourne pendant 2 minutes. C'est à ce moment que, relisant ton code, je vois le etc etc etc Ouille! s'il y en a 3, l'explication est sans doute là !!

Quoi qu'il en soit, si tu veux qu'on trouve une solution un tant soit peu pertinente, il faut, soit joindre un bout de fichier, soit fournir des explications complémentaires sur ce qui figure en feuille "compétences", dans les cellules D53 et suivantes (tiens, il n'y aurait rien en D56!?) ... un filtre automatique, tu y as déjà pensé, j'imagine?

Bonjour et bienvenue sur le forum

La macro, c'est bien mais c'est encore mieux avec le fichier qui va avec...

Bye !

Bonjour et merci pour votre interet à mon probleme.

Voici le fichier en question ...

Pour plus d'explication :

J'ai 11 eleves.

- Dans la feuille "compétences" se trouve un tableau avec la liste des compétences que je dois leur faire travailler.

- Dans l'onglet "OBSERVATIONS" se trouve un tableau dans lequel j'evalue mes eleves.

Ce tableu est dupliqué plus de 200 fois (1 par competence) ce qui rend le fichier tres lourd puisqu'il descend jusqu'aux cellule 4000 voire plus.

J'ai donc Creer un moteur de recherche en VBA (voire Module 1)

ce que je voudrais c'est n'afficher que le tableau de la compétence choisie dans le menu deroulant du moteur de recherche.

>>> Quand je rentre l'integralité des infos dans la macro : ca rame ! = PROBLEME NUMERO 1

- Dans l'onglet "Ines" se trouve le Bilan de l'annee regroupant toutes les competences par eleves ainsi que leur resultats (une sorte de synthese de l'onglet "OBSERVATIONS" mais par élève)

Ce que je voudrais dans cet onglet c'est que lorsque je clique sur une compétence, cela me ramene sur l'onglet OBSERVATIONS avec le tableau correspondant.

L'idee est donc de cliquer sur une competence dans la feuille "Ines" exemple en E53, et que cela me filtre l'onglet "OBSERVATIONS" avec uniquement le tableau de la competence liée.

>>>>La idem, lorsque je rentre toutes les infos dans la macro sur la feuille "Ines" j'ai un message "Erreur de Compilation, procédure trop grande" = PROBLEME NUMERO 2

Voila j'espère que c'est assez clair ...

Encore merci pour le temps que vous y passerez

IMPORTANT : Le fichier joint est une version allégée !! Normalement il fait 2,5MO, il y a une feuille par élève, la feuille OBSERVATIONS comporte normalement TOUTES les compétences et descend jusqu 'aux cellules 4000 et quelques.

Mais sur le site c'est limité a 500 MO

Re-bonjour,

Bonjour gmb,

Je propose de prendre les problèmes un à la fois. Si ton "moteur de recherche" correspond au code dans le Module1, à quoi sert la procédure Worksheet_Change dans le module de la feuille Observations? Celui-ci se déclenche à chaque modification de ta cellule de recherche ... et je ne comprends pas la comparaison que tu y fais avec la colonne D de la feuille compétences

le Worksheet_Change() me sert pour afficher ou masquer le tableau en fonction du résultat du moteur de recherche ...

Apres ne maitrisant pas trop le VBA c'est un code que j'ai trouvé sur le net et adapté a mon besoin donc il est fort probable qu'il existe plus "propre" et plus simple comme solution

Tant que ca fait le job je suis ouvert a toutes suggestions

En gros la reference à la feuille "compétences" me sert à eviter de taper le nom des competences car elles sont parfois tres longues ... et la comparaison et bien c'est toujours pour le masquage/affichage du tableau de la competence.

En gros gros si le choix dans la liste de R4 = Compétences D53 et bien il masque tout sauf les cellules § à 27 qui contiennent le dit tableau.

C'est plus clair ?

Nico44044 a écrit :

C'est plus clair ?

Euh ... chuis pô certain

Tu veux bien laisser ton module de côté un instant, remplacer ta Sub WorkSheet_Change par ce qui suit, et sélectionner une valeur dans ta liste?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, [R4]) Is Nothing Then
If Target = "" Then Rows("6:5000").EntireRow.Hidden = False: Exit Sub
Rows("6:5000").EntireRow.Hidden = True
    On Error Resume Next
    lig = Application.Match([R4].Value, [B:B], 0)
If lig = 0 Then MsgBox "pas trouvé ...": Exit Sub
Cells(lig, 1).Resize(20, 1).EntireRow.Hidden = False
End If
End Sub

Tu peux faire plusieurs sélections successives. Pour tout réafficher, efface le contenu de R4.

Est-ce que "ça fait le job"? Teste maintenant sur ton "vrai" fichier (celui avec 4000 lignes) pour vérifier si la vitesse d'exécution est acceptable.

Bon ca à l'air de fonctionner

Reste le probleme sur la feuille "Ines" ...

En gros sur cet Feuille, je veux qu'en cliquant sur une competence cela fasse la meme chose ...

Que cela m'emmene sur la page Observation et que cela cache tout sauf la competence sur laquelle j'ai cliqué .

La solution que j'avais choisi etait d'envoyer la valeur de la cellule cliqué dans OBSERVATIONS R4 mais ca fait un code à rallonge et me met le fameux code d'erreur "Erreur de Compilation, procédure trop grande" .

il faudrait trouver mieux que :

If Not Intersect(Target, Range("E53")) Is Nothing Then

With Sheets("OBSERVATIONS")

.Range("R4") = Target

.Select

End With

End If

répété 200 fois ...

Une idée ?

Peut etre un truc du genre avec " pour i= " ?

Dans le module de la feuille INES, essaie:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, [E52:E289]) Is Nothing Then
Cancel = True
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
With Sheets("OBSERVATIONS")
    .Activate
    .Rows("6:5000").EntireRow.Hidden = True
    On Error Resume Next
    lig = Application.Match(Target.Value, .[B:B], 0)
    If lig = 0 Then MsgBox "pas trouvé ...": Exit Sub
    .Cells(lig, 1).Resize(20, 1).EntireRow.Hidden = False
    With ActiveWindow
        .ScrollRow = lig
        .ScrollColumn = 1
    End With
End With
End If
End Sub

L'activation de la macro sur simple sélection d'une cellule (compte tenu du volume de données) risque encore de faire "ramer"

Je propose ici le double-clic, mais il faudra tester calmement et vérifier qu'il n'y a pas de cas imprévus!

Il faudra peut-être penser, lors de l'événement Deactivate de la feuille Observations, à réafficher toutes les lignes

en enlevant l'hyperlink dans la colonne E et en double cliquant effectivement ca fonctionne tres bien .

Du coup il n'y a plus de concordance avec ce qui est sélectionné en R4 c'est ca ?

Moi j'etais passé par la ne sachant pas faire autrement ...

Je teste ca en conditions reelles avec toutes les formules rentrées et je reviens vers toi.

1000 merci encore pour ton temps.


Est ce que je peux t'embeter pour un dernier truc ?

Je sais pas si tu as remarqué mais dans la feuille "OBSERVATIONS" , quand tu cliques sur le nom de l'eleve dans le tableau (Ines en l'occurence) il y a un hyperlink qui cherche la competence en question dans la feuille Bilan de l'eleve et la met en evidence.

J'ai fais ca pour qu'il y ait une sorte de lien bidirectionnel qui permette facilement de passer d'un feuille à l'autre sans avoir a chercher a chaque fois dans le tableau.

Est ce que tu pense qu'il y a un moyen de le faire en double clic et pas en simple clic a cause de l'hyperlink ?

Comme ca ca ferai raccord avec le double clic mis en place grace a ton nouveau code sur la feuille "INES" et on met du double clic partout dans le projet.

Bonsoir,

Désolé, je n'avais pas vu que tu avais ajouté une question.

Attention tout de même à ne pas créer une "usine à gaz" (ton fichier semble déjà être un peu "lourd")

Ajoute ce bout de code (c'est toujours un peu le même principe, tu verras) dans le module de la feuille "Observations", en "compagnie" de la Sub WorkSheet_Change qui s'y trouve déjà, teste et dis-nous ce qu'il en est

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 2 Then Exit Sub
If (Target.Row - 7) Mod 22 <> 0 Then Exit Sub

If Target.Cells(1, 1) = "" Then Exit Sub
Cancel = True
On Error Resume Next
test = Sheets("" & Target.Cells(1, 1)).Index
If IsEmpty(test) Then MsgBox "Feuille " & Target.Cells(1, 1) & " pas trouvée": Exit Sub
With Sheets("" & Target.Cells(1, 1))
    .Activate
    On Error Resume Next
    lig = Application.Match(Target.Offset(-1, 0), .[E:E], 0)
    If lig = 0 Then MsgBox "pas trouvé ...": Exit Sub
    With ActiveWindow
        .ScrollRow = lig
        .ScrollColumn = 1
    End With
    .Cells(lig, 5).Activate
End With

End Sub

On y est presque !!!

Ton code fonctionne parfaitement mais ... uniquement pour Ines !

Normalement j'ai aussi une Feuille "Bilan" pour chaque eleves qui porte le nom de l'eleve en question a chaque fois.

Donc en gros si je click sur "Rhema" cela doit faire la meme chose que pour Ines et m'emmener sur la compétence dans la feuille "Rhema".

J'ai 11 eleves donc x11 ...

J'ai tenté de dupliquer ton code et de le modifier :

"If Target.Column <> 8 Then Exit Sub" au lieu de "If Target.Column <> 2 Then Exit Sub" pour passer de la colonne "B" à "H"

Mais ca ne marche pas ... je supose que c'est pas ca qu'il faut modifier.

j'ai compris que "If (Target.Row - 7) Mod 22 <> 0 Then Exit Sub" sert à commencer a la ligne 7 et a descendre 22 par 22.

Pour le reste j'ai besoin de ton aide une derniere fois

Merci bcp

Nico44044 a écrit :

Ton code fonctionne parfaitement mais ... uniquement pour Ines !

Suis-je bête Un peu de précipitation et vlan! Désolé
Nico44044 a écrit :

j'ai compris que "If (Target.Row - 7) Mod 22 <> 0 Then Exit Sub" sert à commencer a la ligne 7 et a descendre 22 par 22

Et tu as parfaitement raison. Il faut faire le même genre de chose pour les colonnes et donc remplacer
If Target.Column <> 2 Then Exit Sub

... Par

If (Target.Column - 2) Mod 6 <> 0 Then Exit Sub

Une adaptation supplémentaire à faire un peu plus loin:

lig = Application.Match(Cells(Target.Row - 1, 2), .[E:E], 0)

... J'espère n'avoir rien oublié cette fois

Ca fonctionne parfaitement !!

Mon fichier est fini j'ai juste une dernière requête (qui devrai être facile pour toi vu ton niveau en VBA)

Je souhaiterai mettre les photos de mes élèves dans l'onglet "OBSERVATIONS"

mais plutôt que de les recopier 200 fois x le nombre d'élève je voudrais l'automatiser avec une macro.

Je voudrais créer un onglet "Elèves" dans lequel je mettrai les photos "sources"

Et ensuite qu'automatiquement la photo en "G6" dans "Elèves" se retrouve automatiquement dans "OBSERVATIONS" en cases "F19","F41","F63" ......etc jusqu'a "F4397". (Pour l'élève numéro 1)

Idem pour "G7" dans "Elèves" ----> "OBSERVATIONS" en "L19","L41" jusqu'à "L4397". (Pour l'élève numéro 2)

J'ai 11 élèves en tout ...

Une idée ??

Apres ça j'aurais terminé et ce grâce à toi

Bonjour,

Si je peux me permettre ... depuis le départ, tu as adopté une "logique de répétition" (tes 1200 lignes de code dans la Sub WorkSheet_Change, tes tableaux en feuille Observations reproduits pour les 11 élèves et les +/- 200 compétences, soit environ 2.200 fois le même petit tableau, etc.) Aujourd'hui tu envisages aussi de reproduire les photos des élèves en un nombre inquiétant d'exemplaires ... ton fichier "pesait" déjà 2.5 Mo au départ; qu'en sera-t-il si tu y ajoutes plusieurs dizaines d'images!?

En plus de la taille du fichier et de son côté "usine à gaz" que j'ai déjà évoqué, il faut imaginer que ton fichier risque d'évoluer ... tu devras peut-être modifier une formule dans tes 2000 tableaux (celles de la plage E20:E24, par exemple), insérer une ligne supplémentaire ou toute autre manipulation qui deviendront rapidement fastidieuses (pour ne pas dire pire). Le risque d'erreurs augmentera encore avec la complexité.

Il vaudrait mieux adopter une logique "base de données": un seul et unique tableau pour stocker tous les résultats, combiné avec une forme quelconque de formulaire pour afficher ces résultats, pour un élève et une compétence. Les formules pour le calcul des moyennes, etc. se faisant à ce moment-là, l'affichage de la photo aussi. Un autre formulaire serait créé pour l'encodage, avec des contrôles de saisie, des listes déroulantes uniquement à ce niveau.

À mon humble avis (mais ce n'est que le mien) ton fichier est déjà un peu "limite". Y ajouter autant de photos que ce que tu imagines devient périlleux. Même si j'avais une idée de comment arriver à faire ce que tu demandes, je crois bien que je m'abstiendrais rdi:

Oui tu as tout à fait raison, j'en conviens… C'est juste que j'ai fait avec les moyens dont je dispose (et avec ton aide ), une base de donnée je ne sais pas faire.... je ne sais même pas si ça peut se faire dans un seul et même fichier Excel en ce qui concerne mon projet

Pour le moment le fichier est certes un petit peu lourd mais il tourne proprement.

Si tu as encore la gentillesse de m'éditer un petit bout de code pour que je puisse tester ça, ce serait super. Après si ça fait trop ralentir le fichier et bien je ferais sans ...

Quoi qu'il en soit je te remercie encore vivement de l'intérêt que tu portes à mon problème.

Et si tu as une idée à me soumettre pour le transformer en base de données ou même une piste, je suis preneur aussi même si j'y connais pas grand chose

J'essaie de repenser le fichier en mode BDD mais je vois pas comment changer l'onglet "OBSERVATIONS" puisqu'il faut une date, une évaluation et un commentaire /jour/eleves/competences ...

Au fil du temps cela rendra forcement le même résultat non ?

J'ai pensé a creer un onglet "saisie" avec des menu déroulant dans des cellules "Competences", "éleves" "note" " date" "commentaire" avec une macro qui "collerai" la ligne à la bonne place dans "OBSERVATIONS" en fonction des paramètres rentrés.

Et je ne ferai apparaitre les photo qu'a ce moment la et non a chaque occurence de chaque tableaux ...

Ca resterai convivial au niveaux de la saisie et plus besoin de manipuler l'onglet "Observation" qui d'usine a gaz passerai a juste un tableau de donnée qui alimenterai les onglet bilan des élèves (comme c'est déjà le cas)

Ca serai compliqué à faire tu penses sachant que les 3/4 du boulot est déjà fait ?

Y aurait "que" l'onglet "saisie" a créer (je minimise pas le travail hein )

Bon j'ai trouvé une autre solution ...

Je vais figer les lignes du haut de la feuille et refaire un peu la mise en page comme ça le nom des élèves (et leur photos) ne seront présent qu'une fois mais visible tout le temps car le scroll démarrera en dessous

Juste besoin de toi pour mettre a jour ton code :

Désormais la cellule sur laquelle je veux cliquer pour envoyer à la compétence sur la feuille bilan est en F18 et non plus en B7.

Et la ligne ou est inscrit la compétence passe en B8 au lieux de B6. J'ai réussi a changer le début du code mais je bloque sur la fin ??

J'arrive pas a lui faire comprendre que la cellule ou chercher la compétence a changer. ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If (Target.Column - 6) Mod 6 <> 0 Then Exit Sub

If (Target.Row - 18) Mod 22 <> 0 Then Exit Sub

If Target.Cells(1, 1) = "" Then Exit Sub

Cancel = True

On Error Resume Next

test = Sheets("" & Target.Cells(1, 1)).Index

If IsEmpty(test) Then MsgBox "Feuille " & Target.Cells(1, 1) & " pas trouvée": Exit Sub

With Sheets("" & Target.Cells(1, 1))

.Activate

On Error Resume Next

lig = Application.Match(Cells(Target.Row - 10, 2), .[E:E], 0)

If lig = 0 Then MsgBox "pas trouvé ...": Exit Sub

With ActiveWindow

.ScrollRow = lig

.ScrollColumn = 1

End With

.Cells(lig, 5).Activate

End With

End Sub

Apres ça promis je te laisse tranquille

Merci d'avance

T'es dur à suivre!

J'en étais encore à essayer d'illustrer ce que je disais ce matin ... ça t'intéresse toujours (ou pas)? et ne va pas dire "oui, oui" pour me faire plaisir, surtout, hein!

Je suppose que ta dernière question concerne la feuille Observations? À première vue, il me semble que ton code est correct ... mais je présume que si ça fonctionnait, tu ne poserais pas la question J'ai un peu de mal à "visualiser" les modifications que tu as faites, tu peux déposer juste une copie de la feuille concernée avec seulement 3-4 tableaux en hauteur?

Oui désolé j'ai beaucoup cojité lol

Ca m'intéresse carrément si tu arrives a me transformer ça en mode BDD je suis preneur a 100% ne serait ce que pour apprendre et voir comment tu as fait !!

Pour ce qui est du code c'est bon il fonctionne (j'avais oublié de renommer les cellules avec le nom de l'onglet ...)

Et en ce qui concerne les modification en fait j'ai simplement mis une seule ligne avec le nom des élèves au dessus du tableau (ligne noire) que j'ai verrouillé comme ça elle est toujours visible même si je scroll.

J'ai retiré toute les autres occurrences de cette ligne du coup.

Je termine ma mise en page et je t'envoie un aperçu

C'est du dépannage mais ça fonctionne. Ca rivaliserai pas avec ta méthode en base de donnée j'imagine mais la ça dépasse mes compétences donc si ça te dérange pas de le faire ou d'au moins me faire une esquisse ... je suis preneur !!

Rechercher des sujets similaires à "aide simplifier macro"