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

Question

Bonjour, 
Configuration: Windows 7 / Internet Explorer 9.0

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.

Mike-31 · Accepted Answer

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.

ccm81 · Answer

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 StringIf 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 SelectEnd IfEnd Sub

Cdlmnt

Kevin · Answer

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

Mike-31 · Answer

Re,

Que crois tu, nous sommes sur Comment ça marche, le forum ou le soleil ne se couche jamais

Kevin · Answer

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

Mike-31 · Answer

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

Kevin · Answer

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 ?

Mike-31 · Answer

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.

Kevin · Answer

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

Kevin · Answer

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

ccm81 · Answer

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

Kevin · Answer

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

Kevin · Answer

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 ?

ccm81 · Answer

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

Mike-31 · Answer

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

Mike-31 · Answer

Re, 

effectivement si J163 pilote le code et contient une formule cela ne fonctionnera pas.

Quel est la formule en J163

Kevin · Answer

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.

Mike-31 · Answer

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

Excel - Masquer certaines lignes en fonction d'une cellule

18 réponses

Discussions similaires