Excel - Masquer certaines lignes en fonction d'une cellule [Résolu/Fermé]

Signaler
-
 Kevin -
Bonjour,


je suis en train de faire un fichier de calcul lié à mon travail et je souhaitrais que certaines lignes se masquent automatiquement en fonction de la valeur d'une cellule.

En gros la cellule qui doit piloter est L151
Si L151 = "Cas 1"
alors il faudrait que les lignes 181 à 191 et 192 à 201 se masquent automatiquement

Si L151 = "Cas 2 ou 3"
alors il faudrait que les lignes 169 à 180 ainsi que 192 à 201 se masquent

Si L151 = "Cas 4"
alors il faudrait que les lignes 169 à 180 et 181 à 191 se masquent


D'après les divers posts à ce sujet c'est possible.
J'ai essayé de modifier plusieurs macros trouvées sur ce forum mais je ne connais pas du tout la fonctionnalité macro VBA et du coup soit ça ne fait rien, soit ça tourne en bloucle pendant des heures, soit ça masque toutes les lignes même celles que je veux voir.
Bref si quelqu'un peut me filer un coup de main là-dessus ça serait sympa...

Merci d'avance

Si possible ne pas donner la macro sans explication car j'ai plusieurs cas identique sur ce fichier et si je peux me débrouiller "seul" grâce à vous pour faire les autres cas ça serait tip top.
De même, mùais là je pense qu'il n'y aura qu'un terme à modifier, j'ai le cas de colonnes à masquer en fonction d'une autre cellule.
Les cellules pilotes sont auto-renseignées par des formules et ne sont pas dans les lignes (ou colonnes) à piloter.

18 réponses

Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Bonjour,

Clic droit sur l'onglet de ta feuille/Visualiser le code et colle ce code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, [L151]) Is Nothing Then
Cells.EntireRow.Hidden = False
If Target = "Cas1" Then Rows("181:201").EntireRow.Hidden = True
If Target = "Cas2" Or Target = "Cas2" Then Range("169:180,192:201").EntireRow.Hidden = True
If Target = "Cas4" Then Rows("169:201").EntireRow.Hidden = True
End If
End Sub


En cellule L151 crée une liste de validation à part que la cellule soit renseignée par formule dans ce cas il faudra modifier le code


A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
2
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 60769 internautes nous ont dit merci ce mois-ci

Messages postés
9572
Date d'inscription
lundi 18 octobre 2010
Statut
Membre
Dernière intervention
28 septembre 2020
1 905
Bonjour

Un début

Const cel = "L151"
Const LaM1 = "181:191"
Const LaM2 = "192:201"
Const LaM3 = "169:180"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cas As String
If Not Intersect(Target, Range(cel)) Is Nothing Then
Cas = Target.Value
Rows("1:" & Rows.Count).Hidden = False
Select Case Cas
Case "Cas 1": Rows(LaM1).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 2": Rows(LaM3).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 3": Rows(LaM3).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 4": Rows(LaM3).Hidden = True: Rows(LaM1).Hidden = True
End Select
End If
End Sub

Cdlmnt
merci à vous deux ! Pour vos réponse et leur rapidité, je n'attendais rien avant demain... voire plus tard
J'essaie ça demain matin.
Je vous redirai laquelle me convient le mieux, sauf si elle font exactement la même chose ! Enfin je vous redirai tout de même si ça fonctionne et si grâce à ça je peux faire les autres cas dont j'ai besoin dans ce fichier.
Bonne soirée
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Re,

Que crois tu, nous sommes sur Comment ça marche, le forum ou le soleil ne se couche jamais
Les 2 versions de mlacro répondent à mon besoin.
Merci à vous 2 : Mike-31 et ccm81 !

Comme expliqué dans ma demande initiale je vais dupliquer la macro utilisée au moins 2 fois en changeant la cellule pilote (et donc les valeurs contenues dans celle-ci) ainsi que les cellules pilotées.
Il me semble avoir compris d'après les recherches d'hier que lorsqu'on utilise plusieurs fois la même "formule" dans plusieurs macros différentes sur le même onglet ça peut planter et qu'il faut donner un nom à chaque macro.
Comment faut-il procéder pour ce faire ?

Si j'ai des soucis pour remplacer les cellules pilote et pilotées je me permettrai de vous poster la macro modifiée pour avis... j'espère ne pas avoir besoin ce qui voudrait dire que j'ai compris (du moins un minimum)
Bon appétit
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Re,

Pas de problème, tu précisera quel code tu as retenu de sorte à t'apporter le support nécessaire.

Pour le code de ccm (salut) il suffit de changer les valeurs dans les variables en début de code
Pour mon code il faut changer les adresses dans le code mais il est possible d'utiliser les variables en début de code peut être plus facile à modifier.

Dans un premier temps je mets le statut en résolu ce qui n'empêche pas de revenir sur la discussion
J'ai pu modifier la macro de Mike-31 pour arriver à mes fins... mais sur 4 fichiers différents.
Lorsque je regroupe les 4 macros dans un même fichier et que je change n'iimporte laquelle des valeurs pilotes, j'obtiens une boîte de dialogue :
"Erreur de Compilation:
Nom ambigu détecté : Worksheet_Change"

ci-dessous les 4 macros utilisées sur la même feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
'Masque les lignes des opérateurs non utilisés
On Error Resume Next
If Not Intersect(Target, [k13]) Is Nothing Then
Cells.EntireRow.Hidden = False
If Target = "2" Then Rows("43:78").EntireRow.Hidden = True
If Target = "3" Then Rows("55:78").EntireRow.Hidden = True
If Target = "4" Then Rows("67:78").EntireRow.Hidden = True

End If

End Sub
-------------

Private Sub Worksheet_Change(ByVal Target As Range)
'Masque les lignes des essais/opérateur non utilisés
On Error Resume Next
If Not Intersect(Target, [k14]) Is Nothing Then
Cells.EntireRow.Hidden = False
If Target = "2" Then Range("21:28,33:40,45:52,57:64,69:76").EntireRow.Hidden = True
If Target = "3" Then Range("22:28,34:40,46:52,58:64,70:76").EntireRow.Hidden = True
If Target = "4" Then Range("23:28,35:40,47:52,59:64,71:76").EntireRow.Hidden = True
If Target = "5" Then Range("24:28,36:41,48:52,60:64,72:76").EntireRow.Hidden = True
If Target = "6" Then Range("25:28,37:41,49:52,61:64,73:76").EntireRow.Hidden = True
If Target = "7" Then Range("26:28,38:41,50:52,62:64,74:76").EntireRow.Hidden = True
If Target = "8" Then Range("27:28,39:41,51:52,63:64,75:76").EntireRow.Hidden = True
If Target = "9" Then Range("28:28,40:41,52:52,64:64,76:76").EntireRow.Hidden = True


End If

End Sub
--------------

Private Sub Worksheet_Change(ByVal Target As Range)
'Masque les colonnes des pièces non utilisées
On Error Resume Next
If Not Intersect(Target, [k12]) Is Nothing Then
Cells.EntireColumn.Hidden = False
If Target = "10" Then Range("N:W").EntireColumn.Hidden = True
If Target = "11" Then Range("O:W").EntireColumn.Hidden = True
If Target = "12" Then Range("P:W").EntireColumn.Hidden = True
If Target = "13" Then Range("Q:W").EntireColumn.Hidden = True
If Target = "14" Then Range("R:W").EntireColumn.Hidden = True
If Target = "15" Then Range("S:W").EntireColumn.Hidden = True
If Target = "16" Then Range("T:W").EntireColumn.Hidden = True
If Target = "17" Then Range("U:W").EntireColumn.Hidden = True
If Target = "18" Then Range("V:W").EntireColumn.Hidden = True
If Target = "19" Then Range("W:W").EntireColumn.Hidden = True


End If

End Sub
--------------

Const cel = "L151"
Const LaM1 = "181:192"
Const LaM2 = "193:204"
Const LaM3 = "169:180"

'Masque les Cas non utilisés (Calcul PV et TV)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cas As String
If Not Intersect(Target, Range(cel)) Is Nothing Then
Cas = Target.Value
Rows("1:" & Rows.Count).Hidden = False
Select Case Cas
Case "Cas 1": Rows(LaM1).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 2 ou 3": Rows(LaM3).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 4": Rows(LaM3).Hidden = True: Rows(LaM1).Hidden = True
End Select
End If
End Sub
---------------

Je présuppose que c'est parceque les 3 macros utilisent la même "formule".
Comment faire pour remédier à cela ?
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Re,

oui tout à fait, tu ne peux utiliser qu'un seul argument Private Sub Worksheet_Change(ByVal Target As Range)

il faut donc empiler les codes à la suite, et placer les variable soit en début de procédure ou au dessus de la procédure comme ci dessous (JE N'AI PAS TESTE CES CODES, JUSTE POUR T'EXPLIQUER)

Const cel = "L151"
Const LaM1 = "181:192"
Const LaM2 = "193:204"
Const LaM3 = "169:180"
Dim Cas As String

Private Sub Worksheet_Change(ByVal Target As Range)
'Masque les lignes des opérateurs non utilisés
On Error Resume Next
If Not Intersect(Target, [k13]) Is Nothing Then
Cells.EntireRow.Hidden = False
If Target = "2" Then Rows("43:78").EntireRow.Hidden = True
If Target = "3" Then Rows("55:78").EntireRow.Hidden = True
If Target = "4" Then Rows("67:78").EntireRow.Hidden = True
End If
If Not Intersect(Target, Range(cel)) Is Nothing Then
Cas = Target.Value
Rows("1:" & Rows.Count).Hidden = False
Select Case Cas
Case "Cas 1": Rows(LaM1).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 2 ou 3": Rows(LaM3).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 4": Rows(LaM3).Hidden = True: Rows(LaM1).Hidden = True
End Select
End If
If Not Intersect(Target, Range(cel)) Is Nothing Then
Cas = Target.Value
Rows("1:" & Rows.Count).Hidden = False
Select Case Cas
Case "Cas 1": Rows(LaM1).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 2 ou 3": Rows(LaM3).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 4": Rows(LaM3).Hidden = True: Rows(LaM1).Hidden = True
End Select
End If
End Sub

A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
Messages postés
9572
Date d'inscription
lundi 18 octobre 2010
Statut
Membre
Dernière intervention
28 septembre 2020
1 905
Salut Mike

Avec un exit sub à la fin de chaque if not intersect .... on devrait gagner un ou deux millièmes de seconde ;-)
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Salut ccm,

Oui certainement mais comme je le disais au début du post c'était un exemple d'empilement et je n'ai ni testé ni interprété le code qu'il faudrait, soit poursuivre ta proposition avec Select Case ou avec la cellule active Target.
Mais bon on va laisser travailler dans son intérêt d'apprendre, notre ami Kevin
Bonne après midi à toi
Mike-31
Je suis dessus... enfin disons que le sujet n'est pas clos mais pas le temps de continuer ce fichier avant la semaine prochaine, d'autres sujets plus urgents.
Merci pour votre aide jusque là, et bonne fin de semaine
J'ai pris sur moi d'essayer pendant ma pause,... conscience professionnelle (sans compter l'envie d'apprendre et comprendre) quand tu nous tiens...
Bref voici le code complet :

Const cel = "k150"
Const LaM1 = "180:191"
Const LaM2 = "192:203"
Const LaM3 = "169:179"
Dim Cas As String

Private Sub Worksheet_Change(ByVal Target As Range)

'Masque les colonnes des pièces non utilisées
On Error Resume Next
If Not Intersect(Target, [k12]) Is Nothing Then
Cells.EntireColumn.Hidden = False
If Target = "10" Then Range("N:W").EntireColumn.Hidden = True
If Target = "11" Then Range("O:W").EntireColumn.Hidden = True
If Target = "12" Then Range("P:W").EntireColumn.Hidden = True
If Target = "13" Then Range("Q:W").EntireColumn.Hidden = True
If Target = "14" Then Range("R:W").EntireColumn.Hidden = True
If Target = "15" Then Range("S:W").EntireColumn.Hidden = True
If Target = "16" Then Range("T:W").EntireColumn.Hidden = True
If Target = "17" Then Range("U:W").EntireColumn.Hidden = True
If Target = "18" Then Range("V:W").EntireColumn.Hidden = True
If Target = "19" Then Range("W:W").EntireColumn.Hidden = True
End If
'---------------------------------------------------

'Masque les lignes des opérateurs non utilisés
On Error Resume Next
If Not Intersect(Target, [k13]) Is Nothing Then
Rows("18:80").Hidden = False
If Target = "2" Then Rows("43:78").EntireRow.Hidden = True
If Target = "3" Then Rows("55:78").EntireRow.Hidden = True
If Target = "4" Then Rows("67:78").EntireRow.Hidden = True
End If
'---------------------------------------------------

'Masque les lignes des essais/opérateur non utilisés
On Error Resume Next
If Not Intersect(Target, [k14]) Is Nothing Then
'Cells.EntireRow.Hidden = False en commentaire ou supprimé car démasque toutes les lignes
If Target = "2" Then Range("21:28,33:40,45:52,57:64,69:76").EntireRow.Hidden = True
If Target = "3" Then Range("22:28,34:40,46:52,58:64,70:76").EntireRow.Hidden = True
If Target = "4" Then Range("23:28,35:40,47:52,59:64,71:76").EntireRow.Hidden = True
If Target = "5" Then Range("24:28,36:41,48:52,60:64,72:76").EntireRow.Hidden = True
If Target = "6" Then Range("25:28,37:41,49:52,61:64,73:76").EntireRow.Hidden = True
If Target = "7" Then Range("26:28,38:41,50:52,62:64,74:76").EntireRow.Hidden = True
If Target = "8" Then Range("27:28,39:41,51:52,63:64,75:76").EntireRow.Hidden = True
If Target = "9" Then Range("28:28,40:41,52:52,64:64,76:76").EntireRow.Hidden = True
End If
'---------------------------------------------------

'Masque les Cas non utilisés (Calcul PV et TV)
If Not Intersect(Target, Range(cel)) Is Nothing Then
Cas = Target.Value
Rows("151:204").Hidden = False
Select Case Cas
Case "Cas 1": Rows(LaM1).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 2 ou 3": Rows(LaM3).Hidden = True: Rows(LaM2).Hidden = True
Case "Cas 4": Rows(LaM3).Hidden = True: Rows(LaM1).Hidden = True
End Select
End If
'---------------------------------------------------

End Sub


Grâce à vous et en décryptant à ma manière le principe j'y suis arrivé, en peu de temps.
Seul "hic" dans ce code la macro "nb d'essai / opérateur" est prioritaire par rapport à "nb opérateur" du coup j'ai du passer en commentaire la ligne de commande qui démasque toutes les lignes au début de "nb d'essais / opérateur".

Bref ça fonctionne comme je le souhaitais à la base !
Un grand merci à Mike-31 et ccm81 pour leur aide et explications !

A moins qu'il existe une solution pour éviter que l'une des macro soit prioritaire sur l'autre (sans rentrer les nombreux cas, de mixage des 2, possibles un par un) sinon pour moi le sujet est clos.
Bonne fin de semaine à tous.
Kevin
Bonjour à tous,
je m'en remet de nouveau à vous concernant ce fichier et pour une raison similaire, d'où l'intérêt de rester sur le même fil.

J'ai fait une petite erreur dans mon tout premier message :
"Les cellules pilotes sont auto-renseignées par des formules" : CE N'EST PAS VRAI

Jusque là je ne m'en était pas rendu compte (bien vu l'aveugle !) mais...
Mes cellules pilotes (pour les cas présentés jusque là) sont saisies par l'utilisateur du fichier, même si la saisie est restreinte via une liste de validation.
Bref avec les codes ci-dessus ça fonctionne.


Or à un endroit dans ce fichier j'ai le cas réel où c'est une cellule pilotée par une formule (qui renvoie la valeur "1" ou "2"). Et dans ce cas, même en "adaptant" (avec mes très faibles connaissances qui se limitent à des modif mineures de vos macros) les 2 types de macros (If Target / Select Case) vues plus haut : ça ne fonctionne pas automatiquement.
Je m'explique :
- la cellule pilote passe bien de 1 à 2 seule [ =si(blabla;1;2) ]
- mais rien ne se masque automatiquement
- cependant, lorsqu'on appui sur "F2" puis "entrée" dans cette cellule pilote, là, la macro s'exécute et les lignes souhaitées s'affichent tandis que d'autres se masquent.
- = idem à ci-dessus si au lieu d'une formule c'est l'utilisateur qui saisit manuellement "1" ou "2" dans la cellule pilote (ça c'est logique).

Bref j'en déduis que pour VBA, un résultat de formule dans une cellule n'est pas l'équivalent d'une saisie manuelle (ou choix manuel dans une liste).

Y-a-t'il une astuce pour y arriver ?
En écrivant cette demande je me dis qu'en demandant à VBA de sélectionner la cellule pilote en début de macro ça pourrait suffire...

Merci d'avance
Kevin
Messages postés
9572
Date d'inscription
lundi 18 octobre 2010
Statut
Membre
Dernière intervention
28 septembre 2020
1 905
peut être comme ceci

- la cellule pilote passe bien de 1 à 2 seule [ =si(blabla;1;2) ]
Donc c'est la/les cellule/s figurant dans blabla qui changent donc les prendre en compte dans le if not intersect .....

Cdlmnt
Merci ccm81 pour ta réponse.

En remontant aux données d'origine qui font bouger toutes les cellules "formules" j'arrive à :
si une cellule située dans : H10:H12 ; K12:K14 ; D19:W28 ; D32:W41 ; D45:W54 ; D58:W67 ; D71:W80 est manuellement changée alors le résultat de J158 [ =si(blabla;1;2) ] doit être regardé et en fonction de sa valeur :
1 : lignes 170:186 et 210:226 doivent se masquer
2 : lignes 187:203 et 227:243 doivent se masquer


On Error Resume Next
If Not Intersect(Target, [j163]) Is Nothing Then
Rows("170:243").EntireRow.Hidden = False
If Target = "1" Then Range("170:186,210:226").EntireRow.Hidden = True
If Target = "2" Then Range("187:203,227:243").EntireRow.Hidden = True
End If

Si je comprend bien la deuxième partie de ma macro est bonne (logique car fonctionne si changement manuel) et je n'aurais qu'à incorporer les cellules qui sont manuellement changeables dans "Target". Mais comment ?

Merci d'avance
du coup j'ai tenté :

On Error Resume Next
If Not Intersect(Target, Range("H10:H12,K12:K14,D19:W28,D32:W41,D45:W54,D58:W67,D71:W80")) Is Nothing Then
Rows("170:243").EntireRow.Hidden = False
If Cells("j163") = 1 Then Range("170:186,210:226").EntireRow.Hidden = True
If Cells("j163") = 2 Then Range("187:203,227:243").EntireRow.Hidden = True
End If


Mais il me cache toutes les lignes tout le temps... (au moins il m'en cache)
Où se trouve mon erreur ?
Messages postés
9572
Date d'inscription
lundi 18 octobre 2010
Statut
Membre
Dernière intervention
28 septembre 2020
1 905
Peut être que ça vient de là?
alors le résultat de J158 [ =si(blabla;1;2) ]
If Cells("j163") = 1 Then Range("170:186,210:226").EntireRow.Hidden = True
erreur de frappe.. Il s'agit bien de J163
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Re,

Si c'est la cellule qui pilote la macro, il faut écrire

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, [J163]) Is Nothing Then
Rows("170:243").EntireRow.Hidden = False
If Cells("J163") = 1 Then Range("170:195,210:226").EntireRow.Hidden = True
If Cells("J163") = 2 Then Range("187:203,227:243").EntireRow.Hidden = True
End If
End Sub

par contre si c'est les cellule de la colonne J

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Union([H10:H12], [K12:K14], [D19:W28], [D32:W41], [D45:W54], [D58:W67], [D71:W80])) Is Nothing Then
Rows("170:243").EntireRow.Hidden = False
If Cells("J163") = 1 Then Range("170:195,210:226").EntireRow.Hidden = True
If Cells("J163") = 2 Then Range("187:203,227:243").EntireRow.Hidden = True
End If
End Sub


c'est bien la cellule J163 qui doit piloter la macro
Mais cette cellule est une formule, ce qui visiblement n'a pas le même effet que si c'est un changement manuel.
Je vois ça demain et vous redis

Merci à vous 2 pour votre grande et appréciable aide sur ce fichier !
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Re,

effectivement si J163 pilote le code et contient une formule cela ne fonctionnera pas.

Quel est la formule en J163
Re,
J163 : =si(J158<=J162;"1";"2")
J158 fait appel à d'autres cellules formule, idem pour J162.

Si on remonte jusqu'à des cellules rentrées manuellement on obtient la plage :
Union([H10:H12], [K12:K14], [D19:W28], [D32:W41], [D45:W54], [D58:W67], [D71:W80])
Et là si n'importe laquelle de ces cases est changée ça peut faire évoluer le résultat final.
Messages postés
17232
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
22 septembre 2020
4 257
Re,

colle dans les propriétés de ta feuille ce code et teste

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Union([H10:H12], [K12:K14], [D19:W28], [D32:W41], [D45:W54], [D58:W67], [D71:W80])) Is Nothing Then
Rows("170:243").EntireRow.Hidden = False
If [J163] = 1 Then [170:195,210:226].EntireRow.Hidden = True
If [J163] = 2 Then [187:203,227:243].EntireRow.Hidden = True
End If
End Sub
Merci Mike31 !!!
C'est exactement ce qu'il me fallait. Enfin j'ai du remettre 186 au lieu de 195 mais ça fonctionne parfaitement comme je le souhaitait.
Grâce à ça mon fichier fait exactement ce que je voulais !
Il faut donc prendre en compte uniquement les cellules que l'utilisateur change manuellement et utiliser "Union".
Grand merci !

Merci aussi à ccm81 pour son aide.

Sujet totalement clos !