Verrouillage partielle d'un classeur Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Répondre
e
edlede
Membre dévoué
Membre dévoué
Messages : 745
Appréciations reçues : 6
Inscrit le : 4 novembre 2015
Version d'Excel : 2013

Message par edlede » 22 mai 2016, 19:18

Bonjour à tous,

Encore novice en VBA, je requière votre attention, pour un petit problème sur une macro

J'aimerai verrouiller qu'un certain nombre d'onglets

j'ai trouvé cette macro qui me permet de verrouiller tous les onglets
For i = 1 To Sheets.Count
    With Sheets(i)
      .Protect Password:="mdp"
      
    End With
  Next
j'aimerai que les onglets commençant par
 
                    If Left(WS.Name, 8) = "Synthese" Then
                    If Left(WS.Name, 1) = "_" Then
                    If Left(WS.Name, 6) = "Modele" Then
Ne soient pas verrouillés !!

Pour les autres onglets (les verrouillés) j'aimerai qu'une plage de cellules restent modifiables. comme dans l'exemple suivant qui fonctionne que pour un onglet
Sheets("ENC_BR8").Select
   
    Range("O10:X2744").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protection.AllowEditRanges.Add Title:="mdp", Range:=Range( _
        "O10:X2744")
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    ActiveSheet.EnableSelection = xlNoRestrictions
Merci d'avance pour vos idées ou vos solutions!
Modifié en dernier par edlede le 24 mai 2016, 21:28, modifié 1 fois.
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'569
Appréciations reçues : 174
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 22 mai 2016, 21:36

bonjour,
D'abord un peu de vocabulaire :
On ne verrouille pas plus un classeur qu'un onglet.
Seules les cellules d'une feuille peuvent être verrouillées. (Elles le sont d'ailleurs toutes par défaut.)
Le verrouillage des cellules est inactif tant que la feuille n'est pas protégée.
Pour permettre la modification et l'accès de certaines cellules quand la feuille sera protégée, il faut d'abord déverrouiller les cellules voulue, avant de protéger la feuille.
Les feuilles et les classeurs peuvent eux être protégés.
S'il n'y a que 3 feuilles à ne pas protéger, il suffit de les mettre complètement à gauche dans la liste des onglets. Ensuite tu utilisse la macro que tu indiques pour protéger les autres feuilles :
For i = 4 To Sheets.Count
With Sheets(i)
ainsi les 3 premières feuilles seront évitées.

La protection des feuilles à pour seul effet de protéger les cellules verrouillées, les objets et d'interdire ou d'autoriser certaines opérations énumérées au moment de la protection...
Si toutes les autres feuilles ont la même plage non verrouillée, tu peux ensuite mettre ton code de déverrouillage et tu termines à la fin par la protection.
'... ici ton code de déverrouillage des cellules et les indications complémentaires
.Range("O10:X2744").Locked = False 'par exemple
.Range("O10:X2744").FormulaHidden = False
.Protection.AllowEditRanges.Add Title:="mdp", Range:=.Range("O10:X2744")
.Protect Password:="mdp",DrawingObjects:=True, Contents:=True, Scenarios:=True
 End With
 Next
Nota : il est inutile de sélectionner les cellules ou les feuilles

La protection du classeur à pour seul effet d'interdire le déplacement des onglets : les 3 premiers doivent toujours rester à leur place et d'empêcher la suppression et l'ajout de nouvel onglet.
A+

[Edit]En l'absence de classeur joint code non vérifié...
Question portant sur VBA : Je ne répond pas si la question ne comporte pas le classeur KIVABIEN avec.
e
edlede
Membre dévoué
Membre dévoué
Messages : 745
Appréciations reçues : 6
Inscrit le : 4 novembre 2015
Version d'Excel : 2013

Message par edlede » 23 mai 2016, 22:10

Merci pour les précisions linguistiques, pour information les 3 types d'onglets, synthèse, , et modèle représentent une trentaine d'onglet.
Je suis sur la construction d'un fichier de 300 ko mais c est chaud.
Peut être que je pourrais l envoyer par mail si c'est possible .

Encore merci pour les pistes
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'569
Appréciations reçues : 174
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 24 mai 2016, 09:08

Bonjour,
Sur ce genre de forum la précision linguistique est essentielle. Sinon si on parle des langues et qu'on ne se comprend pas, c'est mal barré...
De même que l'utilisation des balises "Code" qui permet de faire ressortir les éléments de code sans être obligé d'aller à la pêche...

Tu pourrais déjà commencer par tester la macro telle que je te l'ai réorganisée : (en mettant les 3 feuilles que tu veux préserver le plus à gauche dans ton classeur)
Sub Test()
Dim i
For i = 4 To Sheets.Count
With Sheets(i)
'... ici ton code de déverrouillage des cellules et les indications complémentaires
.Range("O10:X2744").Locked = False 'par exemple
.Range("O10:X2744").FormulaHidden = False
.Protection.AllowEditRanges.Add Title:="mdp", Range:=.Range("O10:X2744")
.Protect Password:="mdp",DrawingObjects:=True, Contents:=True, Scenarios:=True
 End With
 Next
End Sub
A ce stade et par exception je ne pense pas que l'envoie du classeur soit essentiel : ou ça passe, ou ça ne passe pas.
En principe ça doit passer parce que je ne sème pas des erreurs de sémantique à chaque ligne...
Cependant il peut subsister des bugs que seul toi peut générer.
Donc commence par tester. Si tu as une erreur, tu la décris et ça devrait marcher.
Pour le reste, (les attributs de feuille) toi seul peut connaître ce qui te convient ou pas.

Nota : On procède rarement comme ça : D'habitude on crée une feuille modèle dont on verrouille ou pas certaines cellules et qu'on protège une fois avec les attributs voulus, Tri, Selection... Les attributs de protection sont permanents et transmissible (par héritage)donc après yapuka dupliquer la feuille pour que toutes les feuilles aient les mêmes attributs.
Ensuite on fait juste une macro de protection (juste avec le mot de passe) et basta...
Mébon si tu as commencé comme ça, pourquoi pas... (J'y vois quand même un inconvénient, ça veut dire que tu as bricolé tes feuilles individuellement donc il y des chances pour qu'il y ait des "faux jumeaux")
Enfin il faudra protéger (ou pas...) ton classeur mais ça c'est une autre macro...

Encore une précision je suis un petit peu réticent sur le verrouillage des feuilles sauf celle qui s'appelle "toto,titi,tata..." parce que en l'absence de protection du classeur, le nom de la feuille peut être modifié (même si la feuille est protégé...) et ça quand ça arrive, c'est parfois moins perceptible que le déplacement d'une feuille... (D'où l'importance de la protection du classeur qui permet -ou pas- de renommer ou de déplacer les feuilles...)
A+

A+
Question portant sur VBA : Je ne répond pas si la question ne comporte pas le classeur KIVABIEN avec.
e
edlede
Membre dévoué
Membre dévoué
Messages : 745
Appréciations reçues : 6
Inscrit le : 4 novembre 2015
Version d'Excel : 2013

Message par edlede » 24 mai 2016, 21:12

Bonsoir,

Merci encore pour les explications par contre j'ai manisfestement pas tous compris....

Lorsque je lance la macro elle s’arrête ici
Sub Test()
Dim i
For i = 20 To Sheets.Count
With Sheets(i)
'... ici ton code de déverrouillage des cellules et les indications complémentaires
.Range("O10:X2744").Locked = False 'par exemple
je démarre mon compte à 20 car les 19 premiers onglets du classeur sont fixé par cette macro.
Dim a As Integer, b As Integer
Application.ScreenUpdating = False

For a = 1 To Sheets.Count
    For b = a + 1 To Sheets.Count
        If UCase(Sheets(a).Name) > UCase(Sheets(b).Name) Then
            Sheets(b).Move Before:=Sheets(a)
        End If
    Next b
Next a

    Sheets("Sources").Move Before:=Sheets(1)
    Sheets("Bienvenue").Move Before:=Sheets(2)
    Sheets("Modele_CALE").Move Before:=Sheets(3)
    Sheets("Modele_CALS").Move Before:=Sheets(4)
    Sheets("Modele_Cen").Move Before:=Sheets(5)
    Sheets("Modele_Enc").Move Before:=Sheets(6)
    Sheets("Modele_Mic").Move Before:=Sheets(7)
    Sheets("Modele_Pce").Move Before:=Sheets(8)
    Sheets("Modele_Pip").Move Before:=Sheets(9)
    Sheets("Modele_PDP").Move Before:=Sheets(10)
    Sheets("Modele_Rev").Move Before:=Sheets(11)
    Sheets("Modele_Son").Move Before:=Sheets(12)
    Sheets("Modele_Synthese_PDP").Move Before:=Sheets(13)
    Sheets("Synthese des centrifugeuses").Move Before:=Sheets(14)
    Sheets("Synthese des enceintes").Move Before:=Sheets(15)
    Sheets("Synthese des microscopes").Move Before:=Sheets(16)
    Sheets("Synthese des pipettes").Move Before:=Sheets(17)
    Sheets("Synthese des réveils&minuteurs").Move Before:=Sheets(18)
    Sheets("Synthese des sondes").Move Before:=Sheets(19)
End Function
mais du coup tous mes onglets commençant par " _ " se retrouve en dernière position, et j'aurai également besoin de les avoirs déverrouillés...je ne vois pas comment les fixer à gauche, de plus le nombre d' onglet " _ " augmente régulièrement. il me semblait plus simple de pouvoir les exclure de la macro de verrouillage.

Voilà pour la premiere partie des problemes :?

galopin01 a écrit : Mébon si tu as commencé comme ça, pourquoi pas... (J'y vois quand même un inconvénient, ça veut dire que tu as bricolé tes feuilles individuellement donc il y des chances pour qu'il y ait des "faux jumeaux")
Enfin il faudra protéger (ou pas...) ton classeur mais ça c'est une autre macro...
A+
Re bonsoir,

encore 2 petites questions :

Qu'appelle tu faux jumeaux?

et si je verrouille le classeur, je ne pourrais plus supprimer les onglet "temporaires" que je génère pour les impressions?

si j'ai bien compris il faudra que je déverrouille / verrouille le classeur en debut et fin de macro dans le cas de mes onglets temporaires?
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'569
Appréciations reçues : 174
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 24 mai 2016, 22:17

Bonsoir,

Il faudrait que je revoie ça à tête reposée, mais si... on peut bien faire comme tu dis... Je trouve ça pas trop top mébon...
Vu comme je commence à entrevoir ton truc, je me pencherai dessus demain matin à tête reposée.
Moi j'essaierai de mettre un caractère générique au début des noms de feuilles à ne pas protéger. (Un espace par exemple...)
Comme ça il n'y aurait qu'une seule condition : Pas d'espace comme premier caractères... Ça serait toujours ça de gagné !
Sinon on pourrait aussi travailler sur les CodeName de tes feuilles : On les modifie rarement involontairement...
Bon enfin tu aura compris que j'aime pas trop travailler sur le nom des feuilles ! Néanmoins je te bricolerai ça demain....

Pour les faux jumeaux laisse tomber si tes feuilles à protéger sont les copies de modèles yorapa de pb...
Pour la déprotection du classeur, si tu génères des feuilles temporaires c'est bien sûr...
A+
Question portant sur VBA : Je ne répond pas si la question ne comporte pas le classeur KIVABIEN avec.
e
edlede
Membre dévoué
Membre dévoué
Messages : 745
Appréciations reçues : 6
Inscrit le : 4 novembre 2015
Version d'Excel : 2013

Message par edlede » 24 mai 2016, 22:25

Ok merci veux tu une copie par mail? j'ai une version light du fichier d'environ 4 Mo, si tu veux voir ca avec toutes les infos sous les yeux.

Pöur le verrouillage du classeur le pb se pause pour tous les onglets générer finalement, et également pour ma macro de reclassement des onglets. c'est à réfléchir. :idea:
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'569
Appréciations reçues : 174
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 24 mai 2016, 22:41

Bonjour,
4Mo ça passera pas en mail... Passe le sur cjoint et tu me donnes le lien par mail...
A+
Question portant sur VBA : Je ne répond pas si la question ne comporte pas le classeur KIVABIEN avec.
e
edlede
Membre dévoué
Membre dévoué
Messages : 745
Appréciations reçues : 6
Inscrit le : 4 novembre 2015
Version d'Excel : 2013

Message par edlede » 24 mai 2016, 22:50

Et voilà

pour info
mot de passe moderateur = ed
mot de passe verrouillage onglet = mdp

http://www.cjoint.com/c/FEyuWpZMFTL

Merci pour le temps déjà consacré à mon probleme.
Avatar du membre
galopin01
Passionné d'Excel
Passionné d'Excel
Messages : 6'569
Appréciations reçues : 174
Inscrit le : 18 septembre 2008
Version d'Excel : 2016
Téléchargements : Mes applications

Message par galopin01 » 24 mai 2016, 22:59

Ok je verrai ça demain.
Bonne nuit.
Question portant sur VBA : Je ne répond pas si la question ne comporte pas le classeur KIVABIEN avec.
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message