Masquer une feuille en fonction du contenu de cellule

Le 19/10/2016 à 18:57

Bonjour,

Je souhaite masquer certaine feuille de mon classeur en fonction du contenu de certaines cellules.

Dans ma feuille "Paramètre" si les cellules O à Z contiennent un x alors on affiche la feuille correspondante

Les ligne de 7 à 24 correspondent aux agents de 1 à 18

Mot de passe vba : xb8r2k85

[size=150][surligner]Le fichier : Lien supprimé par Modérateur (Amadéus)

Merci de mettre un fichier vierge de toute donnée personnelle (Adresse, tel, noms)[/surligner][/size]

Ce code pour masquer

Private Sub Workbook_Open()
With Sheets(Paramètre)
If .Range("O7") = "" And .Range("P7") = "" And .Range("Q7") = "" And .Range("R7") = "" And .Range("S7") = "" And .Range("T7") = "" And .Range("U7") = "" And .Range("V7") = "" And .Range("W7") = "" And .Range("X7") = "" And .Range("Y7") = "" And .Range("Z7") = "" Then Sheets(Agent_1).Visible = False
End If
If .Range("O8") = "" And .Range("p8") = "" And .Range("q8") = "" And .Range("r8") = "" And .Range("s8") = "" And .Range("t8") = "" And .Range("u8") = "" And .Range("v8") = "" And .Range("w8") = "" And .Range("x8") = "" And .Range("y8") = "" And .Range("z8") = "" Then Sheets(Agent_2).Visible = False
End If
If .Range("O9") = "" And .Range("p9") = "" And .Range("q9") = "" And .Range("r9") = "" And .Range("s9") = "" And .Range("t9") = "" And .Range("u9") = "" And .Range("v9") = "" And .Range("w9") = "" And .Range("x9") = "" And .Range("y9") = "" And .Range("z9") = "" Then Sheets(Agent_3).Visible = False
End If
If .Range("O10") = "" And .Range("p10") = "" And .Range("q10") = "" And .Range("r10") = "" And .Range("s10") = "" And .Range("t10") = "" And .Range("u10") = "" And .Range("v10") = "" And .Range("w10") = "" And .Range("x10") = "" And .Range("y10") = "" And .Range("z10") = "" Then Sheets(Agent_4).Visible = False
End If
If .Range("O11") = "" And .Range("p11") = "" And .Range("q11") = "" And .Range("r11") = "" And .Range("s11") = "" And .Range("t11") = "" And .Range("u11") = "" And .Range("v11") = "" And .Range("w11") = "" And .Range("x11") = "" And .Range("y11") = "" And .Range("z11") = "" Then Sheets(Agent_5).Visible = False
End If
If .Range("O12") = "" And .Range("p12") = "" And .Range("q12") = "" And .Range("r12") = "" And .Range("s12") = "" And .Range("t12") = "" And .Range("u12") = "" And .Range("v12") = "" And .Range("w12") = "" And .Range("x12") = "" And .Range("y12") = "" And .Range("z12") = "" Then Sheets(Agent_6).Visible = False
End If
If .Range("O13") = "" And .Range("p13") = "" And .Range("q13") = "" And .Range("r13") = "" And .Range("s13") = "" And .Range("t13") = "" And .Range("u13") = "" And .Range("v13") = "" And .Range("w13") = "" And .Range("x13") = "" And .Range("y13") = "" And .Range("z13") = "" Then Sheets(Agent_7).Visible = False
End If
If .Range("O14") = "" And .Range("p14") = "" And .Range("q14") = "" And .Range("r14") = "" And .Range("s14") = "" And .Range("t14") = "" And .Range("u14") = "" And .Range("v14") = "" And .Range("w14") = "" And .Range("x14") = "" And .Range("y14") = "" And .Range("z14") = "" Then Sheets(Agent_8).Visible = False
End If
If .Range("O15") = "" And .Range("p15") = "" And .Range("q15") = "" And .Range("r15") = "" And .Range("s15") = "" And .Range("t15") = "" And .Range("u15") = "" And .Range("v15") = "" And .Range("w15") = "" And .Range("x15") = "" And .Range("y15") = "" And .Range("z15") = "" Then Sheets(Agent_9).Visible = False
End If
If .Range("O16") = "" And .Range("p16") = "" And .Range("q16") = "" And .Range("r16") = "" And .Range("s16") = "" And .Range("t16") = "" And .Range("u16") = "" And .Range("v16") = "" And .Range("w16") = "" And .Range("x16") = "" And .Range("y16") = "" And .Range("z16") = "" Then Sheets(Agent_10).Visible = False
End If
If .Range("O17") = "" And .Range("p17") = "" And .Range("q17") = "" And .Range("r17") = "" And .Range("s17") = "" And .Range("t17") = "" And .Range("u17") = "" And .Range("v17") = "" And .Range("w17") = "" And .Range("x17") = "" And .Range("y17") = "" And .Range("z17") = "" Then Sheets(Agent_11).Visible = False
End If
If .Range("O18") = "" And .Range("p18") = "" And .Range("q18") = "" And .Range("r18") = "" And .Range("s18") = "" And .Range("t18") = "" And .Range("u18") = "" And .Range("v18") = "" And .Range("w18") = "" And .Range("x18") = "" And .Range("y18") = "" And .Range("z18") = "" Then Sheets(Agent_12).Visible = False
End If
If .Range("O19") = "" And .Range("p19") = "" And .Range("q19") = "" And .Range("r19") = "" And .Range("s19") = "" And .Range("t19") = "" And .Range("u19") = "" And .Range("v19") = "" And .Range("w19") = "" And .Range("x19") = "" And .Range("y19") = "" And .Range("z19") = "" Then Sheets(Agent_13).Visible = False
End If
If .Range("O20") = "" And .Range("p20") = "" And .Range("q20") = "" And .Range("r20") = "" And .Range("s20") = "" And .Range("t20") = "" And .Range("u20") = "" And .Range("v20") = "" And .Range("w20") = "" And .Range("x20") = "" And .Range("y20") = "" And .Range("z20") = "" Then Sheets(Agent_14).Visible = False
End If
If .Range("O21") = "" And .Range("p21") = "" And .Range("q21") = "" And .Range("r21") = "" And .Range("s21") = "" And .Range("t21") = "" And .Range("u21") = "" And .Range("v21") = "" And .Range("w21") = "" And .Range("x21") = "" And .Range("y21") = "" And .Range("z21") = "" Then Sheets(Agent_15).Visible = False
End If
If .Range("O22") = "" And .Range("p22") = "" And .Range("q22") = "" And .Range("r22") = "" And .Range("s22") = "" And .Range("t22") = "" And .Range("u22") = "" And .Range("v22") = "" And .Range("w22") = "" And .Range("x22") = "" And .Range("y22") = "" And .Range("z22") = "" Then Sheets(Agent_16).Visible = False
End If
If .Range("O23") = "" And .Range("p23") = "" And .Range("q23") = "" And .Range("r23") = "" And .Range("s23") = "" And .Range("t23") = "" And .Range("u23") = "" And .Range("v23") = "" And .Range("w23") = "" And .Range("x23") = "" And .Range("y23") = "" And .Range("z23") = "" Then Sheets(Agent_17).Visible = False
End If
If .Range("O24") = "" And .Range("p24") = "" And .Range("q24") = "" And .Range("r24") = "" And .Range("s24") = "" And .Range("t24") = "" And .Range("u24") = "" And .Range("v24") = "" And .Range("w24") = "" And .Range("x24") = "" And .Range("y24") = "" And .Range("z24") = "" Then Sheets(Agent_18).Visible = False
End If
End With
Worksheets("Accueil").Select
End Sub

et celui-ci pour afficher dans chaque feuille d'agent :

With Sheets(Paramètre)
If .Range("O7") <> "" And .Range("p7") <> "" And .Range("q7") <> "" And .Range("r7") <> "" And .Range("s7") <> "" And .Range("t7") <> "" And .Range("u7") <> "" And .Range("v7") <> "" And .Range("w7") <> "" And .Range("x7") <> "" And .Range("y7") <> "" And .Range("z7") <> "" Then Sheets(Agent_1).Visible = True
End If

Mais cela ne fonctionne pas et je ne comprend pas pourquoi

Quelqu'un pourrait-il m'aider ?

Avec le fichier

Le 19/10/2016 à 22:13

Bonjour,

Essaie ainsi :

Option Explicit

Private Sub Workbook_Open()
Dim wb As Workbook
Dim ws As Worksheet
Dim I As Long, k As Long
Dim x As Double

    Application.ScreenUpdating = False

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Paramètre")

    With ws
        For I = 7 To 24
            k = k + 1
            x = Application.CountA(.Range(.Cells(I, "O"), .Cells(I, "Z")))
            If x <> 12 Then wb.Worksheets("Agent_" & k).Visible = False
        Next
    End With

    Application.Goto wb.Worksheets("Accueil").Cells(1)

    Set ws = Nothing: Set wb = Nothing

End Sub

Le 20/10/2016 à 08:26

Cela ne fonctionne pas, j'ai l'erreur "l'indice n'appartient pas à la selection"

Voici le fichier https://www.cjoint.com/c/FJugyTylSDl qui est anonyme, les noms ont été supprimé

Le 20/10/2016 à 09:29

Bonjour,

Assure toi que l'extension de ton fichier est correct (xlsx ?).

Sinon ton classeur est corrompu...

Cdlt.

Le 20/10/2016 à 11:12

Mon fichier est en xlsm et les macros activées

Le 20/10/2016 à 11:35

RE,