Discussion :

[Sylvie_076]

Bonjour à Tous,

Je cherche un moyen d'autoriser l'écriture dans une ou plusieurs cellules selon la date et l'heure.
Par exemple : autorisation d'écrire de la cellule A1 à C12 le jj/mm/aaaa entre 04:00:00 et 11:30:00, puis de la cellule D1 à F12 de 11:30:00 à 18:30:00, et de la cellule G1 à I12 de 18:30 à 02:00:00.
Etc, etc... de jour en jour.

J'ai suivi plusieurs pistes avec validation de données, mais là, je tourne erond.

Merci de votre aide.

Sylvie

[papouclo]

Bonjour,
Sélectionner la place A1:C12 >>> Outil Validation de données >>> Choisir Personnalisé pour Autoriser >>> Entrer comme Formule.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=(MOD(MAINTENANT();1)>=4/24)*(MOD(MAINTENANT();1)<=8,5/24)

puis OK.
Cordialement
Claude

[Sylvie_076]

Bonjour,

Je vous remercie pour votre réponse si rapide.
Je suis rentrée de congés il y a quelques jours et je me replonge à présent sur ma question et votre réponse.
Ce que je ne comprends pas, c'est la définition des horaires :
pour : =(MOD(MAINTENANT();1)>=4/24)
et pour =(MOD(MAINTENANT();1)<=8,5/24)
Quand j'écris chacune des formules dans une cellule, cela donne à 15h45 VRAI pour la première et FAUX pour la seconde.
Lorsqu'elles sont accolées comme cela =(MOD(MAINTENANT();1)>=4/24)*(MOD(MAINTENANT();1)<=8,5/24) cela donne 0.
Pouvez-vous me donner quelques explications ??
Est-il possible de remplacer MAINTENANT par AUJOURDHUI ?
Ou est-il possible de travailler en lien avec un calendrier ?
Je peux vous joindre un fichier si vous le désirez.
Je vous remercie.

Sylvie

[Menhir]

Je pense qu'il y a une erreur dans la formule de papouclo : pour être conforme à ton exemple, il faudrait changer le 8,5 en 11,5 (11h30).

L'idéal (c'est du moins comme ça que je ferais perso) serait de mettre les deux valeurs horaires dans des cellules adjacentes en y faisant référence dans ta formule par des références relatives.
Parce que, comme ça, tu pourrait avoir la même formule de validation de cellule pour toutes tes cellules et qu'il est plus facile de modifier la valeur de deux cellules que des fonction dans une validation de cellule.
Ce serait aussi plus clair puisque tu pourrais utiliser le format heure.

[papouclo]

Bonjour,
Merci menhir pour avoir signalé mon erreur ! J'ai intérêt à nettoyer mes lunettes pour lire les prochains messages.

Sylvie, je vais essayer de répondre le plus clairement possible à tes questions mais tu aurais certainement une explication plus exhaustive en lisant le tutoriel
Utiliser les fonctions Date et Heure sous Excel 2007.
Indépendamment des formats employés (date, heure,...) Excel retient une durée quelconque sous forme d'un nombre (disons décimal) avec comme unité le jour.
J'écris cette réponse après avoir entré dans une cellule d'Excel la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MAINTENANT()

et la réponse dans la cellule est 25/07/2014 17:46
Mais si je demande l'affichage au format Standard, je peux lire : 41845,74041
N'oublions pas que l'unité est le jour .
Donc la partie entière 41845 montre qu'il y a 41845 jours de passés depuis le 01/01/1900 (cette date correspondant à l'entier 1).
La partie décimale 0,74041 montre que depuis le 25 juillet 0h ,il y a eu une durée passée de 74 041 /100 000 jours soit 17 heures et 46 minutes.
Si j'avais entré la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= AUJOURDHUI()

j'aurais eu comme réponse seulement la date 25/07/2014, soit en passant au format Standard l'entier 41845.
Donc pour répondre à ta question, la fonction AUJOURDHUI ne te donnera aucune indication horaire et n'est d'aucun secours pour ton problème d'entrée de données selon la tranche horaire de la journée.

La fonction MAINTENANT renvoie donc l'instant précis (date et heure ) donné par le temps système de l'ordinateur.
Si l'on veut la date seulement , il suffit d'en prendre la partie entière au moyen par exemple de la fonction ENT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= ENT ( MAINTENANT() )

Si l'on veut l'heure, il suffit d'en prendre la partie décimale avec deux formules possibles :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MAINTENANT() - ENT ( MAINTENANT() )

ou encore :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= MOD ( MAINTENANT() ; 1)

Bon ! Il s'agit de comparer l'heure de la saisie à 4h du matin . L'unité étant le jour, 1 h représente 1/24 jour donc 4h correspond à 4/24 jour.
Si l'on veut que l'heure de saisie soit supérieure à 4h, on écrira : MOD ( MAINTENANT() ; 1) > 4/24
Et si l'on veut que l'heure de saisie soit inférieure à 11 h 30 ou encore 11,5 h on écrira : MOD ( MAINTENANT() ; 1) < 11,5/24 (c'est l'erreur signalée par menhir).
Si l'on veut que l'heure soit comprise entre 4h et 11h 30, on a donc la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= (MOD ( MAINTENANT() ; 1) > 4/24) * (MOD ( MAINTENANT() ; 1) < 11,5/24)

Si l'on tente de saisir des données à 9h15 , la formule renvoie la valeur 1 (9h15 est bien supérieur à 4h et inférieur à 11h30) et la saisie est possible.
Si l'on tente de saisir des données à 15h45, la formule renvoie la valeur 0 (15h45 est supérieur à 4h mais n'est pas inférieur à 11h30) donc la saisie de données sur la plage A1 : C12 sera interdite.
Ouf ! Je ne suis pas sûr d'avoir tout éclairci.
Si tes tranches horaires sont immuables, tu peux écrire la formule comme donnée et réfléchir aux modifications pour les autres plages.
Si tu penses que les bornes des tranches horaires risquent d'être modifiées, pense au conseil de Menhir : écrire par exemple 4:00 dans BA1 et 11:30 dans BB1.
La formule deviendrait alors :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= (MOD ( MAINTENANT() ; 1) > $BA$1) * (MOD ( MAINTENANT() ; 1) < $BB$1)

et il est plus facile de changer les bornes dans les deux cellules.
Menhir, utiliser des références relatives pour recopier la formule d'une plage sur l'autre suppose une réflexion sur la périodicité des numéros de colonnes des plages, ce qui est possible mais ...
Cordialement
Claude

[Sylvie_076]

Bonsoir ou Bonjour,

J'ai compris toutes les explications que vous m'avez envoyé et je vous remercie.
Les tests effectués sont totalement satisfaisants.
Deux interrogations subsistent pour normalement finaliser.
Je vous joint un fichier basique "Test_Ecriture" (squelette avant réalisation du classeur).

Tout d'abord, je souhaite que l'autorisation d'écriture selon les heures définies se fassent également en fonction de la date du jour si celle-ci est égale à AUJOURDHUI(). Le fonction AUJOURDHUI() convient mieux que MAINTENANT() car plus simple.

J'ai eu la surprise ensuite de pouvoir supprimer du texte dans les cellules où l'heure de saisie n'est pas autorisé.
J'ai effectué des essais avec Protéger la Feuille, en supprimant le Verrouillage avec le menu format de cellules.
Cela n'a aucun effet
Comment éviter cela ? Si un utilisateur écrit le dimanche 27 juillet 2014 entre 10h00 et 15h00, il ne doit pas pouvoir supprimer le texte des cellules utilisant la validation de données du jour et des jours précédents.

Pouvez-vous m'éclairer sur ces deux points ?

Je vous remercie encore du temps que vous m'accorder. J'espère que vous trouverez mon fichier joint.

Sylvie

.Test_Ecriture.xlsx

[papouclo]

Bonjour,
Des critères de validation de données n'interdissent généralement en rien qu'une cellule de la plage soit vide, d'où la possibilité d'effacer ce qui est écrit dans une cellule.
Pour empécher de telles mésaventures, je ne vois que la protection de la feuille avec le déverrouillage des cellules de la plage où l'on est autorisé à écrire.
Je n'ai vu que du VBA pour cela d'où le classeur joint avec deux procédures : l'une autorisant la saisie dans la bonne plage ; l'autre validant la saisie en reverrouillant les cellules qui avaient été déverrouillées,
2 variables ayant été déclarées avec l'instruction Public en tête du module.
En cas d'une saisie impossible (date ne correspondant pas à la feuille active ou heure ne correspondant à aucune tranche horaire), la procédure de Saisie renvoie un message d'avertissement.
Les feuilles servant à la saisie doivent toutes avoir la même structure (en particulier, les dates doivent être écrites en colonne G et les plages réservées aux différentes tranches horaires doivent commencer 4 lignes plus bas qu'une date).
Les codes sont ci dessous et sont certainement améliorables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Public Lig As Long, Col As Long

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub saisie()
 
Dim zoneSaisie As Range
Dim ceJour As Date, heureSaisie As Double, calculTranche As Long, datesValables As Range
Dim deb1 As Double, fin1 As Double, deb2 As Double, fin2 As Double, deb3 As Double, fin3 As Double      ' bornes des tranches horaires
 
    ActiveSheet.Unprotect
    ActiveSheet.Cells(1, 12).ClearContents
 
    On Error GoTo Impossibilite
 
    deb1 = Sheets("Calendrier").[AC11].Value: deb2 = Sheets("Calendrier").[AC12].Value: deb3 = Sheets("Calendrier").[AC13].Value
    fin1 = Sheets("Calendrier").[AD11].Value: fin2 = Sheets("Calendrier").[AD12].Value: fin3 = Sheets("Calendrier").[AD13].Value
 
    With ActiveSheet
        heureSaisie = Time
        .[F1].Value = heureSaisie
        ceJour = Date
        Lig = Evaluate("=match(C1,G1:G800,0)") + 4      ' Ligne de la 1ère cellule de la zone de saisie
 
    calculTranche = (heureSaisie >= deb1) * (heureSaisie <= fin1) + 2 * (heureSaisie >= deb2) * (heureSaisie <= fin2) + 3 * (heureSaisie >= deb3) * (heureSaisie <= fin3)
    Select Case calculTranche
        Case 1
            Col = 2                                     ' Colonne de la 1ère cellule de la zone de saisie
        Case 2
            Col = 7
        Case 3
            Col = 12
        Case 0
            GoTo Impossibilite
    End Select
 
    .Range(Cells(Lig, Col), Cells(Lig, Col).Offset(5, 0)).Select
        Selection.Locked = False
        Selection.FormulaHidden = False
 
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 
    End With
 
    Exit Sub
 
Impossibilite:
    With ActiveSheet
        If Err = 1004 Then
            .[L1].Value = "Saisie impossible pour cette date"
        Else
            .[L1].Value = "Saisie impossible pour cette heure"
        End If
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With
 
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub validation()
 
With ActiveSheet
    .Unprotect
    If Lig = 0 Or Col = 0 Then
        .[L1].Value = "Commencer par appuyer sur le bouton Saisie"
    Else
        .Range(Cells(Lig, Col), Cells(Lig, Col).Offset(5, 0)).Select
        Selection.Locked = True
        Selection.FormulaHidden = True
    End If
    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 
End With
 
End Sub

Vu la demande [Toutes versions] du titre, j'ai enregistré le classeur au format .xls (ce qui évite de plus de devoir le comprimer) pour pouvoir vérifier qu'il n'y avait pas de forte incompatibilité avec une version antérieure à 2007.
Cordialement
Claude

[Sylvie_076]

Bonsoir ou bonjour,

Je vous remercie de votre réponse et du fichier Test_Protection.xls que vous m'avez confié et du temps passé.
Je l'ai testé et j'ai essayé également de comprendre les macros.
Cette solution va, je pense, poser des problèmes aux utilisateurs.
Je vais régler le souci de suppression de saisie de texte hors plage horaire par des copies de fichiers qui seront gérées chaque jour, à la fin de chaque plage horaire.

J'essaye depuis votre réponse de repartir de la formule = (MOD ( MAINTENANT() ; 1) > 4/24) * (MOD ( MAINTENANT() ; 1) < 11,5/24), en essayant d'imbriquer le test de la date. C'est à dire SI la date du calendrier est égale à AUJOURDHUI, alors on autorise la saisie dans les cellules appropriées.
C'est un échec. Un manque de logique peut-être.

Pouvez-vous m'aider sur ce dernier point ?

Le message rédigé auparavant est parti trop vite.

Merci.

Sylvie.

P.S. : je peux vous renvoyer le fichier Test_Ecriture si besoin.

[papouclo]

Bonjour,
A condition de supprimer la ligne 4 dans le tableau du classeur donné en exemple, pour la validation de données des plages des colonnes B:E correspondant à la tranche horaire 04h00 - 09h00 ,
remplacer l'ancienne formule =(MOD(MAINTENANT();1)>=4/24)*(MOD(MAINTENANT();1)<=9/24) par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=(MOD(MAINTENANT();1)>=4/24)*(MOD(MAINTENANT();1)<=9/24)*(ligne()>=(equiv(aujourdhui();G:G;0)+4))*(ligne()<=(equiv(aujourdhui();G:G;0)+9))

et modifier de même les formules correspondant aux deux autres tranches horaires.
Cordialement
Claude

[Sylvie_076]

Bonjour

Je vous remercie de la solution que vous m'avez apporté, ainsi que du temps passé pour me répondre.
J'ai effectué des tests ces deux derniers jours : ÇA MARCHE...

Pouvez-vous m'éclairer sur la seconde partie de la formule :
(LIGNE()>=(EQUIV(AUJOURDHUI();G:G;0)+4))*(LIGNE()<=(EQUIV(AUJOURDHUI();G:G;0)+9))
Concernant =(EQUIV(AUJOURDHUI();G:G pas de souci.
Ce qui n'est pas compris c'est :
LIGNE()>=
+4
LIGNE()<=
+9

Comment fait-on également pour mettre l'annotation RÉSOLU ?

MERCI à nouveau.

Sylvie

[papouclo]

Bonjour,
La partie de formule EQUIV(AUJOURDHUI();G:G;0) renvoie le numéro de la ligne où est inscrite la date du jour.
Les plages de feuille où la saisie sera autorisée seront écrites sous cette ligne et elles commenceront 4 lignes plus bas pour se terminer 9 lignes plus bas.
D'où la condition posée pour le numéro de ligne d'une cellule pour avoir le droit d'y écrire :
(ligne() >= ligne_de_la_date_du_jour + 4) * (ligne() <= ligne_de_la_date_du_jour + 9 )
ou plus exactement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(ligne() >=EQUIV(AUJOURDHUI();G:G;0) + 4) * (ligne() <= EQUIV(AUJOURDHUI();G:G;0) + 9 )

Pour ce qui est de la deuxième question, j'ai trouvé en cliquant sur la mention Règle juste sous la ligne d'identification cette réponse :

Quand vous avez obtenu la réponse à votre question, merci d'ajouter l'état [Résolu] à votre titre en cliquant sur le bouton en bas à gauche de la page.

Cordialement
Claude

[Sylvie_076]

Bonjour ou Bonsoir,

J'ai tout compris. Je vous REMERCIE pour votre disponibilité.

Sylvie

[Sylvie_076]

Bonjour à Toutes et Tous,

Je reprends la discussion arrêté le 9 août 2014.

J'ai trouvé une astuce pour que la suppression de la saisie soit impossible dans les zones paramétrées.

Dans le fichier ci-joint, les espaces de saisies se retrouvent à leur droite avec une mini colonne : colonnes F L et R.

Ces 3 colonnes sont paramétrées dans Format Cellule "Onglet Protection" en Verrouillée : la case est cochée.
Ces 3 colonnes n'ont pas de paramètres Validation de données.

Pour l'autorisation d'écriture (4h00 - 9h00) les colonnes BCDE sont paramétrées dans Format Cellule "Onglet Protection" en non Verrouillée : la case n'est pas cochée.
Ces 4 colonnes après fusion ont la formule suivante dans Validation de données :
=(MOD(MAINTENANT();1)>=4/24)*(MOD(MAINTENANT();1)<=9/24)*(LIGNE()>=(EQUIV(AUJOURDHUI();O:O;0)+5))*(LIGNE()<=(EQUIV(AUJOURDHUI();O:O)+10)).

Une fois ces opérations effectuées, il faut fusionner les cellules. Exemple : Ligne 7, fusionner les colonnes BCDE avec la mini colonne F.
Après la fusion, le Format de Cellule Onglet protection se retrouve avec un statut Verrouillé et non Verrouillé.
Il faut répéter cette fusion pour chaque ligne.

Une fois que la feuille est protégée, il sera impossible de modifier et surtout EFFACER la saisie en dehors de la plage horaire autorisée.

Merci pour votre lecture.

Signée Sylvie