Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

Menibelus

XLDnaute Nouveau
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
VB: 
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 :
VB: 
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 ?
 

Pièces jointes

  • Planning 2017_v001.xlsm
    1.9 MB · Affichages: 82
Dernière édition:
Trier par date Trier par votes

Staple1600

XLDnaute Barbatruc
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.
 
Vote positif 0 Vote négatif

Menibelus

XLDnaute Nouveau
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
VB: 
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
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
Bonsoir à tous

Pas de commentaires sur cette suggestion ?
 
Vote positif 0 Vote négatif

Paf

XLDnaute Barbatruc
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+
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

  • Question
XL 2021 Exportation feuille msgbox yes no avec module Thisworkbook
2
Réponses
21
Affichages
944
Nicolas JACQUIN
L
  • Question
XL 2021 Comment utiliser la colonne d'une adresse en Private Sub - svp ?
Réponses
3
Affichages
427
vgendron
O
  • Question
Microsoft 365 Excel [VBA]. Erreur d’exécution 424 Objet requis sur une concaténation
Réponses
3
Affichages
375
Oneida
S
  • Question
XL 2019 Problème dans un code vba
Réponses
2
Affichages
532
snoopy07
S
J
  • Question
XL 2021 Lien hypertexte dans forme rectangle
Réponses
2
Affichages
358
Phil69970
B
  • Résolu(e)
Microsoft 365 Besoin d'aide pour faire fonctionner 2 codes VBA ensemble
Réponses
2
Affichages
292
BenExcel
B
  • Question
Microsoft 365 Trouver et attribuer une plage de cellule en fonction de la cellule active
Réponses
6
Affichages
373
Jojo973
H
  • Question
Autres mise a jour automatique feuille excel vba
Réponses
2
Affichages
513
HANN
H
J
  • Question
XL 2019 recuperer plage de cellule sur feuille differente
Réponses
2
Affichages
569
juju91
J
H
  • Question
Microsoft 365 Besoin aide : Erreur code macro . Problème est résolu
Réponses
7
Affichages
831
heart clean
H
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…