verrouiller des cellules en fonction de la valeur de l'une d'entre elles

[Patrick75]

Bonjour à tous,

J'ai besoin d'un coup de main à propos du projet suivant :
mon tableau Excel enregistre des dépenses effectuées dans différentes monnaies (à l'aéroport par exemple).
Les monnaies sont en colonne, les dépenses sont sur les lignes.

Lorsque j'enregistre une dépense dans une monnaie, je voudrais que les cellules vides sur la même ligne prennent un format particulier ou bien la valeur "0" ou mieux, soient verrouillées en écriture. Ceci afin d'éviter les erreurs d'enregistrement.

Ci-joint mon exemple.
J'ai lu plusieurs questions a priori similaires mais les VBA sont trop complexes pour que je puisse adapter les exemples au mien.

merci de prendre du temps pour m'aider
Patrick

 

[Victor21]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Bonjour Patrick75.

Utilisez la validation de données (Données, Validation de données, Autoriser : personnalisé).
En E7, à recopier vers le bas : =somme(F7;G7)=0
En F7, à recopier vers le bas : =somme(E7;G7)=0
En G7, à recopier vers le bas : =somme(E7;F7)=0
Pour l'aspect réversible, il suffit d'effacer la valeur entrée pour en entrer une autre dans une colonne différente.
Une mise en forme conditionnelle peut également signaler les cellules vides :
Sélectionnez la zone à partir de E7, Accueil, mise en forme conditionnelle, formule : =e7=0 et choisissez la couleur de fond qui vous convient.

 

[Patrick75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Merci Victor21.
Je comprends ta proposition jusqu'à choisir "Personnalisé" dans le menu déroulant dans la fenêtre "Validation des données"
Ensuite, je ne comprend pas :

En E7, à recopier vers le bas : =somme(F7;G7)=0
En F7, à recopier vers le bas : =somme(E7;G7)=0
En G7, à recopier vers le bas : =somme(E7;F7)=0
je vois l'espace pour écrire dans la fenêtre "Validation des données" mais que dois-je y écrire précisément ? Dois-je sélectionner une cellule au préalable ?
Merci de bien vouloir m'expliquer
Patrick

 

[job75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Bonsoir les deux Patrick

Dans le ThisWorkbook du fichier joint :

Private Sub workbook_Open()
Feuil1.Protect "toto", UserInterfaceOnly:=True 'adaptez le mdp
Verrouillage
End Sub

Dans le code de Feuil1 :

Private Sub Worksheet_Change(ByVal Target As Range)
Verrouillage
End Sub

Dans Module1 :

Sub Verrouillage()
Dim P As Range, Q As Range, R As Range
Set P = Feuil1.[D6].CurrentRegion
P.Resize(Rows.Count - 5).Locked = False
P.Interior.Pattern = xlSolid
Set P = P.Offset(1, 1).Resize(P.Rows.Count - 1, P.Columns.Count - 1)
On Error Resume Next
Set Q = P.SpecialCells(xlCellTypeConstants, 1)
Set R = Intersect(P, Q.EntireRow)
R.Locked = True
R.Interior.Pattern = xlGray25
Q.Locked = False
Q.Interior.Pattern = xlSolid
End Sub

Bonne fin de soirée.

 

[Patrick75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Merci Job75
ça m'impressionne tellement ça marche !!
Je ne suis pas forcément capable de reproduire la démarche dans mon fichier d'origine mais je peux au moins bosser dessus puisque mon ficher "Exemple" fonctionne excellemment bien avec ta VBA (je pense que ça s'appelle comme ça
Merci donc et merci aussi à Patrick dit "Victor21".

 

[Victor21]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Re, Patrick75, bonsoir Gérard

Sélectionnez E7 :
Dans la validation de données, saisissez dans la zone "Personnalisé" la formule

=somme(F7;G7)=0

validez, et recopiez E7 jusqu'en E9.
De même pour F7, puis pour G7

 

[Patrick75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Merci Patrick
Pardon pour ma faible compréhension :-(
J'ai suivi ce que tu m'as dit et cela fonctionne très bien. Je trouve bien le message qui s'affiche lorsque l'on veut introduire un autre montant dans la même ligne. Vos deux propositions différentes (à Job75 et à toi) me conviennent très bien.
Bonne soirée,
Patrick

 

[job75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Bonjour les Patrick, le forum,

Il manquait le traitement automatique des bordures, utilisez cette macro :

Sub Verrouillage()
Dim P As Range, Q As Range, R As Range, S As Range, i&, j As Byte
Static derlig& 'mémorise la variable
Set P = Feuil1.[D6].CurrentRegion.Resize(Rows.Count - 5)
P.Locked = False
P.Interior.Pattern = xlNone
On Error Resume Next
Set Q = P(2, 2).Resize(P.Rows.Count - 1, P.Columns.Count - 1)
Set R = Q.SpecialCells(xlCellTypeConstants, 1)
Set S = Intersect(Q, R.EntireRow)
S.Locked = True
S.Interior.Pattern = xlGray25
R.Locked = False
R.Interior.Pattern = xlNone
'---bordures---
i = P.Find("*", , xlValues, , xlByRows, xlPrevious).Row - 4
If i = derlig Then Exit Sub
derlig = i
P.Rows(i + 1).Resize(P.Rows.Count - i).Borders.LineStyle = xlNone
With P.Resize(i)
  For j = 7 To 11
    .Borders(j).Weight = xlThin
  Next
  .Borders(xlInsideHorizontal).Weight = xlHairline
  .Rows(1).Borders(xlEdgeBottom).Weight = xlThin
End With
End Sub

Fichier (2).

Bonne journée et A+

 

[job75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Re,

J'ai testé la macro précédente sur 10000 lignes.

Sur Win 7 Excel 2010 elle s'exécute en 1,5 seconde.

Quand on modifie une cellule c'est trop long.

Il faut donc limiter les déverrouillages/verrouillages aux lignes modifiées.

Les nouvelles macros :

Private Sub workbook_Open()
Application.ScreenUpdating = False
Feuil1.Protect "toto", UserInterfaceOnly:=True 'adaptez le mdp
Verrouillage Feuil1.Cells
Me.Saved = True 'évite l'invite à la fermeture
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Verrouillage Target
End Sub

Sub Verrouillage(Target As Range)
Dim P As Range, Q As Range, R As Range, S As Range, i&, j As Byte
Static derlig& 'mémorise la variable
Set P = Feuil1.[D6].CurrentRegion.Resize(Rows.Count - 5)
If Intersect(P, Target) Is Nothing Then Exit Sub
'---déverrouillage/verrouillage limité aux lignes modifiées---
Intersect(P, Target.EntireRow).Locked = False
Intersect(P, Target.EntireRow).Interior.Pattern = xlNone
On Error Resume Next
Set Q = P(2, 2).Resize(P.Rows.Count - 1, P.Columns.Count - 1)
Set Q = Intersect(Q, Target.EntireRow)
Set R = Q.SpecialCells(xlCellTypeConstants, 1)
Set S = Intersect(Q, R.EntireRow)
S.Locked = True
S.Interior.Pattern = xlGray25
R.Locked = False
R.Interior.Pattern = xlNone
'---bordures---
i = P.Find("*", , xlValues, , xlByRows, xlPrevious).Row - 4
If i = derlig Then Exit Sub
derlig = i
P.Rows(i + 1).Resize(P.Rows.Count - i).Borders.LineStyle = xlNone
With P.Resize(i)
  For j = 7 To 11
    .Borders(j).Weight = xlThin
  Next
  .Borders(xlInsideHorizontal).Weight = xlHairline
  .Rows(1).Borders(xlEdgeBottom).Weight = xlThin
End With
End Sub

Comme on le voit la macro Verrouillage est maintenant paramétrée.

Fichier (3) avec 10000 lignes.

A+

 

[Patrick75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Merci pour le complément
comme d'autres, je suis subjugué par la connaissance d'Excel des personnes qui nous répondent (fort gentiment d'ailleurs)

Bien cordialement,
Patrick

 

[Patrick75]

Re : verrouiller des cellules en fonction de la valeur de l'une d'entre elles

Merci pour le temps passé à formuler ta réponse exhaustive dont le résultat sur le fichier est totalement bluffant pour moi
Re bien cordialement
Patrick