Verrouillage partielle d'un classeur

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!

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é...

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

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+

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?

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+

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.

Bonjour,

4Mo ça passera pas en mail... Passe le sur cjoint et tu me donnes le lien par mail...

A+

Et voilà

pour info

mot de passe moderateur = ed

mot de passe verrouillage onglet = mdp

Merci pour le temps déjà consacré à mon probleme.

Ok je verrai ça demain.

Bonne nuit.

Merci bonne nuit

Bonjour,

Une solution qui répond parfaitement à la demande :

DefBool Y   'Cette instruction doit se trouver sur la première ligne du module
            'Avant toute autre déclaration ,Sub ou Function

Sub galopin()
Dim Y, Y1, Y2, Y3, i% ' % = As integer
   For i = 1 To Worksheets.Count
      With Worksheets(i)
      Y1 = Left(.Name, 8) = "Synthese"
      Y2 = Left(.Name, 1) = "_"
      Y3 = Left(.Name, 6) = "Modele"
      Y = Y1 Or Y2 Or Y3
         If Not Y Then
             .Unprotect Password:="mdp"
             .Range("O10:X2744").Locked = False
             '.Range("O10:X2744").FormulaHidden = False 'inutile (par défaut)
             'ActiveSheet.Protection.AllowEditRanges.Add Title:="Plage autorisée", Range:=Range("O10:X2744") '(inutile)
            .Protect Password:="mdp" 'autres otions inutiles (par defaut)
            .EnableSelection = xlUnlockedCells
         End If
      End With
   Next
End Sub

Nota : La feuille "Deconta a suppr" est hors norme : ( Protect )

A+

Je test cela ce soir, merci pour tout

Merci c'est parfait aucun bug sur ma phase de test, BRAVO galopin01, j'aurai juste une derriere faveur sur le sujet je n'arrive pas à garder les cellules sélectionnables lorsqu’elles ont été verrouillées.

il me semble que dans mon enregistrement de macro le code était :

.Protection.AllowEditRanges.Add Title:="Plage autorisée", Range:=Range("O10:X2744")

mais je ne suis plus bien certain, car j'avoue que ca me stress beaucoup de ne pas pouvoir faire la selection de la cellules même si je sais qu'elle est verrouillée...

Si tu as une dernière idée pour ça je suis preneur. Encore merci pour ta solution rapide ( dire que je galère là dessus depuis presque 3 mois....pas à temps plein mais bon ).

bonsoir,

AllowEditRanges n'a rien à voir dans cette histoire...

C'est .EnableSelection = qui autorise ou non la sélection des cellules verrouillées.

Modifie comme ça :

Sub galopin()
Dim Y, Y1, Y2, Y3, i% ' % = As integer
   For i = 1 To Worksheets.Count
      With Worksheets(i)
      Y1 = Left(.Name, 8) = "Synthese"
      Y2 = Left(.Name, 1) = "_"
      Y3 = Left(.Name, 6) = "Modele"
      Y = Y1 Or Y2 Or Y3
         If Not Y Then
             .Unprotect Password:="ed"
             .Range("O10:X2744").Locked = False
             .EnableSelection = xlNoRestrictions
             '.Range("O10:X2744").FormulaHidden = False 'inutile (par défaut)
             'ActiveSheet.Protection.AllowEditRanges.Add Title:="Plage autorisée", Range:=Range("O10:X2744") '(inutile)
            .Protect Password:="ed" 'autres otions inutiles (par defaut)
         End If
      End With
   Next
End Sub

Attention de bien laisser le DefBool Y en tête du module...

A+

Parfait MERCI et je suis toujours stupéfait du peu de ligne des codes efficaces !! BRAVO encore à toi pour toutes les explications

je Valide le sujet comme résolu,

je me permet de réouvrir le sujet, je voulais savoir si il était possible que la macro ne s'exécute pas sur les onglets déjà vérrouillés.

En effet j'ai testé la macro sur mon fichier complet, et le temps d'excusion dépasse très largement les 5 minutes ( bon le réseau rame à mort !).

je me disais que l'on gagnerai peut etre quelques minutes si la macro ne s'executait que sur les onglets non vérrouillé.

Merci

Bonjour,

Ben... Tu peux toujours essayer !

ça va te donner quelque chose comme ça :

Sub galopin2()
Dim Y, Y1, Y2, Y3, i% ' % = As integer
   For i = 1 To Worksheets.Count
      With Worksheets(i)
      Y1 = Left(.Name, 8) = "Synthese"
      Y2 = Left(.Name, 1) = "_"
      Y3 = Left(.Name, 6) = "Modele"
      Y = Y1 Or Y2 Or Y3
         If Not Y Then
            If .Range("O10:X2744").Locked = True Then
               .Unprotect Password:="mdp"
               .Range("O10:X2744").Locked = False
               .Protect Password:="mdp"
               .EnableSelection = xlUnlockedCells
            End If
         End If
      End With
   Next
End Sub

Bonjour,

Ben... Tu peux toujours essayer !

ça va te donner quelque chose comme ça :

Sub galopin2()
Dim Y, Y1, Y2, Y3, i% ' % = As integer
   For i = 1 To Worksheets.Count
      With Worksheets(i)
      Y1 = Left(.Name, 8) = "Synthese"
      Y2 = Left(.Name, 1) = "_"
      Y3 = Left(.Name, 6) = "Modele"
      Y = Y1 Or Y2 Or Y3
         If Not Y Then
            If .Range("O10:X2744").Locked = True Then
               .Unprotect Password:="mdp"
               .Range("O10:X2744").Locked = False
               .Protect Password:="mdp"
               .EnableSelection = xlUnlockedCells
            End If
         End If
      End With
   Next
End Sub

re j'ai testé

Dim Y, Y1, Y2, Y3, i% ' % = As integer
  For i = 1 To Worksheets.Count
      With Worksheets(i)
      Y1 = Left(.Name, 8) = "Synthese"
      Y2 = Left(.Name, 1) = "_"
      Y3 = Left(.Name, 6) = "Modele"
      Y = Y1 Or Y2 Or Y3
         If Not Y Then
            If .Range("O10:X2744").Locked = True Then
               .Unprotect Password:="mdp"
               .Range("O10:X2744").Locked = False
               .EnableSelection = xlNoRestrictions
               '.Range("O10:X2744").FormulaHidden = False 'inutile (par défaut)
               .Protect Password:="mdp"
               '.EnableSelection = xlUnlockedCells
            End If
         End If
      End With
   Next

lorsque je déverrouille quelques onglets et que je lance la macro, elle s'execute tres vite mais les quelques onglets dévérouiller ne se reverrouille pas...

si je veux que tous soit verrouiller correctement

 If .Range("O10:X2744").Locked = false Then

mais du coup la macro repasse sur tous les onglets.

si tu as une autre idée je suis preneur.

Ah oui non mais !

Bon je t'ai fait un truc un peu téléphoné, parce que tu l'as demandé comme ça... Mais là il faut regarder les choses sous un autre angle... Se pencher un peu sur vos mode opératoire et puis voir ça d'une manière un peu plus professionnelle !

Bon c'est clair que pour un classeur qu'on n'utilise pas trop fréquemment, on peut lancer ce type de macro le midi avant d'aller au gastro, ou le soir en quittant, mais si c'est pour la lancer toutes les 5 minutes ça va pas le faire.

Aujourd'hui surtout sur des classeurs de ce type (et même sur des classeurs plus simple) on utilise des outils un peu plus évolués et on ne déprotège plus les feuilles que très exceptionnellement. (pour des opérations de mise au point de macro la plupart du temps)

Le verrouillage des cellules joue son rôle pour la saisie au quotidien des utilisateurs dans les zones prévues à cet effets et tout le reste est sous traité à des macros qui sont prévues pour travailler sans déprotéger tout le système... et/ou à minima pour reprotéger un feuille déprotégée par inadvertance.

En utilisation normale on utilise UserInterfaceOnly qui permet aux macros de s'exécuter même si la feuille est protégée et les cellules aussi.

Il faut utiliser cet argument dans le Workbook_Open et dans le Worksheet_Activate éventuellement.

Au pire on peut utiliser une macro Déactivate sur chaque feuille, si on est vraiment angoissé (ou un peu bordélique...)

Mais là ce que tu m'a demandé c'est vraiment pour faire une 'tite toilette périodique.

Les macros Workbook_Open,

Workbook_SheetActivate

Workbook_SheetDeactivate

Workbook_BeforeClose

se trouvent dans le module ThisWorkbook et peuvent assurer un contrôle permanent et individuel de l'état des feuilles de manière instantanée. C'est certain que si à chaque fois que tu manipules une feuille tu contrôles l'état de toutes les autres tu n'as pas fini de faire le tour de la machine à café...

Pour illustrer mon propos je te prépare un petit classeur démo dans la soirée...

A+

A+

Rechercher des sujets similaires à "verrouillage partielle classeur"