XL 2016 [Résolu]Masquer une feuille en fonction de cellules

[Menibelus]

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
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 ?

 

[Staple1600]

Bonsoir à tous


Si j'étais moi, je verrai le problème autrement
Des feuilles doivent être masquées selon les utilisateurs
Ce qui peut s'écrire
Des utilisateurs n'ont pas besoin de ces feuilles
Ce qui peut se lire
Selon les utilisateurs, enregistrons des versions sans ces feuilles (donc en les supprimant)
ou
Selon les utilisateurs, enregistrons des copies en PDF des seules feuilles dont ces utilisateurs ont besoin pour faire leur taf.

Ainsi plus besoin de protéger quoique ce soit, pas de crainte de voir les feuilles démasquées par les petits curieux.

 

[Menibelus]

re,


vous le mettez à la place de votre code (celui proposé au post 1).



Option Explicit, en tête de module, signifie que toutes les variables doivent être déclarées. et le message d'erreur est clair.
Juste après Private Sub Workbook_Open(), ajoutez:

Dim i as Integer

A+

J'ai bien modifié le code comme indiqué mais toujours un code erreur "Type défini par utilisateur non défini"
Voici le code complet dans mon ThisWorkbook

Option Explicit
Private Sub Workbook_Open()
Dim i As Integrer
With Sheets(Paramètres)
For i = 7 To 24
  If Application.WorksheetFunction.CountIf(.Range("O" & i & ":Z" & i), "x") = 12 Then
  Sheets("Agent_" & i - 6).Visible = True
  End If
  If Application.WorksheetFunction.CountIf(.Range("O" & i & ":Z" & i), "x") = 0 Then
  Sheets("Agent_" & i - 6).Visible = False
  End If
Next
End With
End Sub
Private Sub workbook_close()
Protéger
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'--- active ce classeur, met en plein écran ---'
  Application.DisplayFullScreen = True
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'--- change de classeur, annule plein écran ---'
  Application.DisplayFullScreen = False
End Sub

 

[Paf]

re

Dim i As Integrer

Dim i as Integer

Peut-être ?

 

[Staple1600]

Bonsoir à tous

Bonsoir à tous


Si j'étais moi, je verrai le problème autrement
Des feuilles doivent être masquées selon les utilisateurs
Ce qui peut s'écrire
Des utilisateurs n'ont pas besoin de ces feuilles
Ce qui peut se lire
Selon les utilisateurs, enregistrons des versions sans ces feuilles (donc en les supprimant)
ou
Selon les utilisateurs, enregistrons des copies en PDF des seules feuilles dont ces utilisateurs ont besoin pour faire leur taf.

Ainsi plus besoin de protéger quoique ce soit, pas de crainte de voir les feuilles démasquées par les petits curieux.

Pas de commentaires sur cette suggestion ?

 

[Menibelus]

re




Peut-être ?

Ben non, excel corrige automatiquement as en As

 

[Paf]

re,

d'accord pour le as en As, mais Excel ne corrige pas integrer en Integer . d'où le message "Type défini par utilisateur non défini", puisqu'il ne connait pas le type integrer

A+

 

[Menibelus]

Effectivement cela fonctionne mieux
Merci beaucoup