Vérouiller une plage de celule en fonction d'une date
Bonjour,
Je tiens d'abord à préciser que je suis novice dans l'utilisation d'Excel et que je ne sais pas programmer.
J'essaye de pouvoir verrouiller les données d'une ligne si la date reprise dans la colonne A de cette ligne est plus petite que la date du jour (par exemple 5 jours avant la date du jour).
J'ai inséré un fichier d'exemple. La date du jour est reprise en A1. Je souhaiterai verrouiller les cases en jaune -> ligne 64(automatiquement demain -> ligne 65).
Merci d'avance pour vos bons conseils!!!
Bonjour,
Par défaut, les cellules sont verrouillées. Il faut donc d'abord les déverrouiller...
Le verrouillage ne produit d'effet que lorsque la feuille est protégée...
Donc, si ta feuille est protégée, ta macro doit commencer par la déprotéger.
Tu n'as pas besoin de date en A1 ! Tu fais une boucle sur la colonne A et tu verrouilles la ligne si la date est inférieure à la date prévue. Et tu reprotèges.
Ce qui donne en gros :
Sub Verrouiller()
Dim dv, i%, n%
With ActiveSheet
.Unprotect
n = .Cells(.Rows.Count, 1).End(xlUp).Row
dv = Date - 4
For i = 3 To n
If .Cells(i, 1) < dv Then
.Rows(i).Locked = True
Else
Exit For
End If
Next i
.Protect
End With
End SubA adapter ou compléter selon cas... mot de passe de protection, verrouiller toute la ligne ou seulement la partie utilisée, etc.
Cordialement.
Merci beaucoup pour votre réactivité!!
J'ai copié la programmation telle quelle dans le VBA Project de la feuille 1.
J'ai veillé à ce que les colonnes A->E soient verrouillées au niveau de la protection - Format de cellules et j'active la protection de la feuille via Révision - Modification.
A ce moment, toutes les cellules sont inaccessibles, même celle autour et après la date du jour, apparemment la macro ne donne pas son effet de déverrouiller les lignes à partir du Today-4.
Le fichiers envoyé n'est qu'un exemple très ressemblant de mon fichiers réel; j'ai de toute façon la date du jour en A1. Je n'ai pas besoin de mot de passe pour verrouiller. Les lignes peuvent être verrouillées dans leur entièreté.
Comme je n'ai pas d'expérience dans le domaine de la programmation, je ne sais pas s'il faut des renseignements complémentaires?
Il est donc fort possible que j'ai raté une étape indispensable!!!
Merci d'avance pour vos conseils avisés,
Dans ton fichier, toutes les cellules étaient déverrouillées. Je suis donc parti de là pour verrouiller en fonction des dates.
Au départ, tu déverrouilles donc toute la feuille, pour pouvoir ensuite verrouiller sélectivement avec une procédure...
Bonjour,
Non, décidément, cela ne fonctionne pas, même si toutes les cellules sont déverouillées à la base. Je sais encore modifier sans problème les lignes précédent le J-3.
J-3 ! Normal !
Tu as fixé de pouvoir modifier jusqu'à J-5 !!!
(par exemple 5 jours avant la date du jour).
En fait comme j'ai interprété ta phrase de pouvoir modifier les 5 derniers jours en incluant le jour J, ma macro vrerrouille ce qui est antérieur à J-4, donc à partir de j-5 et antérieur...
Malheureusement , j'ai testé à partir du 01/01/2017 (c'est un tableau annuel) aucune cellule n'est bloquée, ni avant, ni après!!
Je reprends le fichier que j'ai communiqué !
Au départ, état non verrouillé, on écrit donc partout !
Un petit clic sur le bouton "Verrouiller" !
De la ligne 3 (01/01/2017) jusqu'à la ligne 66 05/03/2017) : plus possible d'écrire !
A partir de la ligne 67 (06/03/2017), on écrit à nouveau !
Nous somme le 10/03 ! Le tableau est donc bien verrouillé jusqu'à J-5 et déverrouillé à partir de J-4 !
Je t'invite donc à faire l'expérience ! Il n'y a qu'à appuyer une fois sur le bouton ! Après avoir ouvert le fichier, naturellement ! Et activé les macros, tout aussi naturellement !
Cordialement.
C'est génial!!!! J'ai compris
Je n'avais pas vu le bouton assigné à la macro.
En pratique, j'arrive à transposer cela dans un fichier avec 1 feuille. Par contre, cela ne fonctionne pas pour les autres feuilles. Or, j'ai besoin de cette macro sur certaines feuilles mais pas sur d'autres. Peut-on adapter la formule pour "bloquer" toutes les feuilles nécessaires à la fois, ou dois-je introduire un bouton dans chaque feuille?
En tout cas mille fois merci parce que j'avance et je n'aurai jamais pu le faire toute seule!!!
Bonsoir,
Si la structure des feuilles concernées est la même, que le verrouillage repose sur des dates positionnées pareillement, la même procédure peut s'appliquer...
On a deux méthodes rapides dans ce cas :
- En plaçant un bouton sur chaque feuille concernée, et en y affectant la même macro, elle s'appliquera sur la feuille dont on clique le bouton...
- Sans mettre de bouton supplémentaire, on peut facilement la modifier pour qu'elle traite en boucle toutes les feuilles concernées...
Cordialement.
Bonjour,
Pour moi, ce serait plus simple effectivement d'introduire directement les feuilles concernées dans la macro de manière à ce le blocage se fasse automatiquement sur toutes les feuilles.
Dans ce cas, comment doit-on procéder? Par n° de feuille, par nom de feuille,...? Quel sera "la traduction" de cela dans le language de programmation?
Je suis impatiente de pouvoir mettre cela sur pied, plus de souci de modification intempestive dans ce fichier partager!!!
Merci d'avance
Voici un adaptation pour traiter simultanément plusieurs feuilles :
Sub VerrouillerFeuilles()
Dim dv, fv, f%, i%, n%
fv = Split("Feuil2;Feuil3;Feuil5;Feuil6", ";") 'A adapter (lister noms de feuilles séparés par un ;)
For f = 0 To UBound(fv)
With Worksheets(fv(f))
.Unprotect
n = .Cells(.Rows.Count, 1).End(xlUp).Row
dv = Date - 4
For i = 3 To n
If .Cells(i, 1) < dv Then
.Rows(i).Locked = True
Else
Exit For
End If
Next i
.Protect
End With
Next f
End SubLe code de verrouillage, entre With et End With est le même.
On a simplement créé en amont un tableau des noms de feuilles concernées, et on le parcourt dans une boucle pour répéter les traitement pour chaque feuille.
Cela suppose évidemment que le code antérieur peut s'appliquer tel quel à toutes les feuilles concernées.
S'il y avait des variantes, il faudrait les examiner afin de voir quelles adaptations permettraient de conserver un traitement global...
Cordialement.
Voilà, voilà,
J'ai mis le code en pratique. Cela marche sauf:
Il m'indique un message d'erreur quand je clique sur le bouton après assignation de la macro: "Run-time error '9': Subscript out of range". Il me laisse le choix entre "End" - "Debug" et "Help".
Quand je clique sur "Debug"
Sub VerrouillerFeuilles()
Dim dv, fv, f%, i%, n%
fv = Split("sheet1;sheet2;sheet4;", ";") 'A adapter (lister noms de feuilles séparés par un
For f = 0 To UBound(fv)
With Worksheets(fv(f))
.Unprotect
n = .Cells(.Rows.Count, 1).End(xlUp).Row
dv = Date - 4
For i = 3 To n
If .Cells(i, 1) < dv Then
.Rows(i).Locked = True
Else
Exit For
End If
Next i
.Protect
End With
Next f
End Sub
Si je ferme VBa par la suite, la macro fonctionne correctement pour les pages concernées (ce sont exactement les mêmes template). Plus de trace de ce message d'erreur.
Encore une petite question: le fait de faire tourner la macro aujourd'hui (16/03/2017) bloque les cellules antérieures au 12/03/2017; est-ce-que demain le 17/03/2017, les cellules seont bloquées automatiquement à partir du 13/03/2017, ou on doit cliquer sur le bouton "vérouiller" tous les jours (Ce qui n'est pas pratique en cas d'absence!!
Bonne journée!!
Bonjour,
fv = Split("sheet1;sheet2;sheet4;", ";")Tu as tapé un point-virgule de trop en listant les feuilles. Ce qui crée un élément de plus dans le tableau, vide.
Lorsque la boucle arrive à cet élément, ce vide ne correspondant à aucun nom de feuille déclenche une erreur 9 (erreur d'indice).
En cas d'erreur, VBA passe en mode Arrêt, si tu as cliqué sur Débogage. Il faut sortir du mode Arrêt (bouton Réinitialiser [petit carré] dans la barre d'outils de l'éditeur) pour que du code puisse à nouveau s'exécuter. L'erreur intervenant à la fin, la procédure s'est donc exécutée sur les feuilles concernées, si tu la réitères, cela ne fera rien de plus mais tu auras le message d'erreur tant que tu n'auras pas supprimé ce ; en trop. Si tu ne l'as pas eu c'est que tu étais encore en mode Arrêt...
Tu peux la faire lancer automatiquement à l'ouverture du classeur. Il n'y aura plus à s'en occuper, et plus besoin de bouton.
Private Sub Workbook_Open()
VerrouillerFeuilles
End Subqui doit être placée dans le module ThisXorkbook.
Cordialement.
- Dans mon fichier réel, les date ne commencent qu'à partir de la cellule A5. Auparavant, ce sont les titres des colonnes. Il bug donc dès la première page. Comment peut-on faire entrer la macro en ligne de compte qu'à partir de A5 (01/01/2017)?
- Dans mon fichier réel, j'ai déjà une petite programmation afin qu'il ouvre toujours sur la page d'accueil, à la date du jour, malgré la dernière sauvegarde. C'est moi qui ai bricolé quelque chose, je ne sais pas si c'est exact mais cela fonctionne:
Private Sub Workbook_Open()
With Worksheets("General")
.Activate
.Columns(1).Find(Date).Select
End With
With Worksheets("Mail-Box & Room")
.Activate
.Columns(1).Find(Date).Select
End With
End Sub
Je ne sais pas comment faire co-exister les deux programmations dans le module "ThisWoorkbook"???
En fait, quand on pense avoir trouvé la solution, c'est plus complexe que cela n'y paraît!!!!
Je pense quand même qu'on est tout prêt d'y arriver!!!
Merci!!
Bonsoir,
Tu ne vas pas m'infliger un code truffé de Select et Activate !
On est parti sur une procédure de verrouillage adaptée à un modèle de feuille où la colonne A contient des dates partant du 01/01/2017 et formant une série strictement chronologique dans la colonne. Le 01/01 étant situé ligne 3.
Dans ce contexte, on n'a besoin d'aucune recherche, on déduit immédiatement la ligne correspondant à la date du jour...
Il convient donc de lister tous les éléments des autres feuilles à traiter qui dérogeraient à ce modèle, de façon à pouvoir faire une adaptation définitive.
Par la même occasion, il serait bon de savoir si la série de dates concerne exclusivement 2017 ou si elle peut se prolonger au-delà, d'une part, et si d'autre part la même procédure doit pouvoir s'appliquer sur d'autres années que 2017.
Cordialement.
Bonjour,
Je crois qu'il y a une petite confusion: il y a deux points disctincts. Je m'explique
- Le premier point: j'ai en effet une colonne A de dates sucessives (qui couvrent toute une année civile). Mais ces dates ne commencent qu'à partir de la ligne 5. Les lignes précédentes comportant le nom des colonnes. (Voir Exemple Test2). Je ne veux bloquer dans l'exemple que les feuilles Mail-Box & Room, General et 1. Notre programmation ne fonctionne pas, je pense, à cause des cellules A1 à A4 qui ne comportent pas cette suite de dates???
- Le deuxième point: je souhaiterais que le document s'ouvre toujours sur la feuille Mail-Box & Room à la date du jour et que la feuille "General" soit également prête à la date du jour. C'est pourquoi j'ai déjà dans mon fichier cette programmation dont j'ai fait la copie qui vaut ce qu'elle vaut puisque c'est un bricolage de mon crû!!!
Je ne veux en aucun cas changer tout le travail qui a déjà été effectué mais essayer de combiner tous les éléments
Effectivement je devrai reproduire ce tableau pour les années à venir mais ils seront dans d'autres fichiers séparés.
J'espère avoir été un peu plus claire dans mes explications...
Bonne journée!!
Bonjour,
Je suppose qu'il y aura 2, 3 et 4 à la suite de 1 ?
Pour ces 4 feuilles, il n'y a lieu de traiter que pendant la période où elles sont actives : avant on ne verrouille pas, et après que tout ait été verrouillé, plus la peine d'y revenir...
Pour la feuille à activer à l'ouverture : MailBox... apparemment [A confirmer], l'activation est à placer dans Workbook_Open, mais l'ajustement visuel des 2 feuilles est à faire dans une proc. SheetActivate.
Je regarde...
Bonjour!!
Effectivement, j'ai 25 tableaux identiques (donc de feuilles de 1 à 25), ensuite quelques autres feuilles dont il ne faut pas tenir compte pour le vérouillage.
Le but est de pouvoir compléter le tableau au jour le jour et de pouvoir faire des modifications à court terme (jour-5) et ensuite de tout figer de manière à ne pas pouvoir faire de gaffe. Je dois pouvoir être sûre des chiffres encodés la semaine précédentes et qu'ils ne bougent plus continuellement.
Comme j'ai dit, la programmation existante est un bricolage , il y a sûrement moyen de faire mieux mais vu mes connaissances au niveau excel...
Tout le code est dans ThisWorkbook
Function LigneDateJour(ws As Worksheet) As Integer
Dim i%, dj As Date
i = LigneDébutDates(ws): dj = Date
With ws
Do While .Cells(i, 1) <= dj
If .Cells(i, 1) = dj Then
LigneDateJour = i
Exit Do
End If
i = i + 1
Loop
End With
End Function
Function LigneDébutDates(ws As Worksheet) As Integer
Dim i%
With ws
Do
i = i + 1
If IsDate(.Cells(i, 1)) Then
If Not .Cells(i, 1).HasFormula Then
LigneDébutDates = i
Exit Do
End If
End If
Loop
End With
End Function
Sub ActiveMBRG(ws As Worksheet)
Dim lgj%
lgj = LigneDateJour(ws)
Application.ScreenUpdating = False
With ActiveWindow
.ScrollRow = lgj
.ScrollColumn = 1
End With
ws.Cells(lgj, 1).Select
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet, lgd%, lgf%
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Tableau"
Case Else
lgd = LigneDébutDates(ws)
lgf = LigneDateJour(ws) - 5
With ws
.Unprotect
.Cells.Locked = False
If lgf >= lgd Then .Rows(lgd & ":" & lgf).Locked = True
.Protect
End With
End Select
Next ws
Set ws = Worksheets("Mail-Box & Room")
If ActiveSheet.Name <> ws.Name Then
ws.Activate
Else
ActiveMBRG ws
End If
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "General", "Mail-Box & Room"
ActiveMBRG Sh
End Select
End SubLa proc. Open se déclenche à l'ouverture. Si d'autres feuilles ne doivent pas être traitées en ce qui concerne le verrouillage, tu mets leurs noms à la suite de "Tableau" (entre guillemets et séparés par des virgules). Toutes les autres feuilles sont traitées.
On recherche individuellement sur chaque feuille la ligne de début des dates et la ligne de la date du jour.
Deux fonctions sont dédiées au renvoi de ces lignes.
On verrouille les lignes de la ligne de début des dates jusqu'à 5 lignes avant la date du jour.
Après verrouillage, la proc. teste si "MailBox & Room" est la feuille active.
Si elle ne l'est pas elle l'active.
La proc. SheetActivate prend alors le relais et lance une procédure auxiliaire ActiveMBRG si la feuille activée est MailBox & Room ou General. Cette proc. recherche également la date du jour pour mettre la ligne en tête du volet principal...
Si MailBox & Room était déjà la feuille active (dans ce cas son activation ne déclencherait pas la proc. SheetActivate) c'est alors Open qui lance ActiveMBRG pour cette feuille. Laquelle procédure sera par la suite lancée pour ces deux feuilles à chaque activation.
Si on souhaite le scroll auto de la ligne date du jour pour d'autres feuilles, il faut les rajouter à la suite dans le Case concerné.
S'il faut l'appliquer pour la majorité des feuilles, alors comme pour Open, on mettra un Case pour les feuilles exclues, et on fera s'appliquer la procédure dans le Case Else.
Cordialement.