Masquer une feuille en fonction du contenu de cellule
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
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
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é
Bonjour,
Assure toi que l'extension de ton fichier est correct (xlsx ?).
Sinon ton classeur est corrompu...
Cdlt.
Mon fichier est en xlsm et les macros activées
RE,
Si tu le dis.
Cdlt.
Donc pas de solution pour le message d'erreur