VB - Verrouillages cellules avec case à cocher

angelspeed · 17 Septembre 2011

Bonjour,

Je saisis des commandes dans une feuille de calcul. Une commande = 1 ligne.

Quand la commande est validée je souhaite la verrouiller. Pour ce faire, je compte rajouter dans une colonne une case à cocher à l'aide de ce script très pratique (pas de moi 🙂) :

Code:

Sub CréerCaseàcocher()
Dim Cellule As Range
For Each Cellule In Range("L4:L100")
    With Cellule
    .Select
    ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height).Select
    End With

    With Selection
    .LinkedCell = Cellule.Offset(0, 1).Address
    .Characters.Text = ""
    '.Characters.Text = "Case" & Cellule.Row
    End With

Next Cellule
End Sub

La feuille est protégée puisque je sais que ça pose problème sinon.

Maintenant le problème :je souhaiterais attribuer une macro à chaque case à cocher pour qu'en cochant celle-ci elle verrouille la commande/une plage de cellules.

Pour être plus précis : case à cocher en L4 verrouille/déverrouille les cellules A4 à K4 (celle en L5, A5 à K5, etc ...)

J'ai trouvé ce script pour verrouiller une cellule/plage de cellule avec une checkbox avec un changement de couleur :

Code:

Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
    With ActiveSheet
        .Unprotect
        .Range("A1").Interior.ColorIndex = 6
        .Range("A1").Locked = True 'Verrouiller la cellule A1
        .Protect AllowFiltering:=True 'protege la feuille
    End With
Else
    With ActiveSheet
       .Unprotect 'Deprotege la feuille
       .Range("A1").Interior.ColorIndex = 2
       .Range("A1").Locked = False 'Déverrouiller la cellule A1
       .Protect AllowFiltering:=True 'Verrouiller la feuille
    End With
End If
End Sub

Le problème c'est comment intégrer ce script dans le premier et implémenter la plage de cellules à verrouiller en fonction de n° de ligne de la checkbox.

La je suis pas assez calé en VB pour voir comment faire.

Merci de votre aide.

Dranreb · 18 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonsoir.
Apparemment cette collection CheckBoxes (élément masqué du langage) est une collection de vieux CheckBox de la barre d'outils formulaire et non de la boite à outils contrôle. Il ne fonctionnent pas du tout comme ceux qui relèvent de la seconde procédure.
Tant mieux, ça vous arrange ici: Le CheckBox doit avoir une propriété OnAction à laquelle vous pouvez affecter le nom d'une procédure d'un module ordinaire à sa création, et dans cette procédure CheckBoxes(Application.Caller).LinkedCell devrait vous rendre l'addresse de la plage à traiter. Essayez de vérifier tout ça par des MsgBox, des points d'arrêt, des espions.
À+

phlaurent55 · 18 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonjour à tous,

ma petite contribution en pièce jointe

à+
Philippe

angelspeed · 18 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonjour,

merci à vous deux pour votre aide. Je vais approfondir les deux pour me perfectionner en vb 🙂

Je reviens vers vous au cas où.

angelspeed · 20 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonjour,

en fait j'ai décidé de dissocier pour finir la création des checkboxes et la fonction de verrouillage en prenant la solution de Philippe, plus simple si ya besoin d'en rajouter par la suite.

A la différence par contre que c'est à partir de la valeur de la cellule cible des checkboxes que le verrouillage s'effectue ou non (valeur VRAI ou FAUX) plutôt qu'un clic droit. J'ai donc pris Worksheet_Change

Par contre ça ne marche pas avec le code que j'ai adapté et je vois pas du tout pourquoi :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Q3:Q100")) Is Nothing Then

ActiveSheet.Unprotect
Target.Value = IIf(Target.Value = "FAUX", "VRAI", "FAUX")
ligne = Target.Row

If Target.Value = "VRAI" Then
    Range(Cells(ligne, 1), Cells(ligne, 15)).Locked = True
Else
    Range(Cells(ligne, 1), Cells(ligne, 15)).Locked = False
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End Sub

Les checkboxes à cocher sont dans la colonne P
Les cellules cibles des checkboxes dans Q
Les cellules à verrouiller de A à O, soit 15 colonnes

Merci de votre aide.

Dranreb · 20 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonsoir.
Philippe vous a proposé une solution intelligente à laquelle j'avais immédiatement pensé moi aussi.
Si je ne vous l'ai pas proposée, c'est parce que j'en ai marre d'en proposer de telles à des demandeurs qui restent ensuite obstinément sur leur idée. Cette fonction repose sur un double clic et n'implique plus de checkbox.
Alors je ne comprends plus rien à votre dernier propos: vous dites que vous adoptez sa solution, tout en continuant à parler de checkbox ...avec des croissants comme disait Fernand RAYNAUD...
À+

angelspeed · 20 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonsoir,

je ne reste pas obstinément sur mon idée puisque les checkbox ne servent à rien mis à part changer la valeur de la cellule cible. Il n'y a pas de code liée à ces checkbox.

Le code de Philippe marche parfaitement et je l'en remercie, cependant pour d'autres utilisations je ne peux pas utiliser le clic droit : ce devra être le résultat de formules qui déterminera le verouillage/déverouillage de la plage de cellules.

La seule différence est par conséquent l'appel de Worksheet_Change plutôt que Worksheet_BeforeRightClick, le reste du script est identique, ce qui doit être le problème d'ailleurs puisque ce n'est pas le clic droit qui change la valeur de la cellule mais une formule.

Alors avant de me lancer vos propos, suffisait de regarder le code de Philippe et le mien pour le constater. Quand on me donne des solutions qui peuvent répondre à mon problème je les prends mais il faut parfois les adapter pour étendre leurs utilisations.

Bonne soirée

angelspeed · 20 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

J'ai réussi à verrouiller les cellules en utilisant la valeur d'une cellule avec ce code adapté de celui de Philippe :

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("R:R")) Is Nothing Then
ActiveSheet.Unprotect
ligne = Target.Row

If Target.Value = "A" Then
    Range(Cells(ligne, 1), Cells(ligne, 15)).Locked = True
    Range(Cells(ligne, 1), Cells(ligne, 15)).Font.Italic = True
Else
    Range(Cells(ligne, 1), Cells(ligne, 15)).Locked = False
    Range(Cells(ligne, 1), Cells(ligne, 15)).Font.Italic = False
End If

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect
End If

End Sub

Je rencontre cependant 2 problèmes :

- impossible d'utiliser les valeurs VRAI/FAUX rendues par des formules excel pour l'appliquer dans le Target.Value. Je ne dois pas connaître la bonne syntaxe. J'ai donc utiliser un "A" pour tester.
- le verrouillage/déverouillage ne s'opère que si je tape le A dans la cellule. Si le A provient d'une formule, cela ne marche pas

Merci de votre aide

Dranreb · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Une Worksheet_Change s'exécute si le contenu d'une cellule change. Le changement de valeur d'une formule qui ne change pas, elle, n'a rien à voir avec ça.
S'il n'y a pas de pièce jointe à votre prochain post qui montre ce que vous voulez faire et pourquoi, je retire cette discution de mes discutions suivies.

phlaurent55 · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Re,

S'il n'y a pas de pièce jointe à votre prochain post qui montre ce que vous voulez faire et pourquoi, je retire cette discution de mes discutions suivies.

@ Dranreb,
je suis du même avis

Comme beaucoup de "questionneurs" sur ce forum, je pense qu' angelspeed a un peu de mal à formuler sa question avec précision
et il me donne l'impression de vouloir manger de la farine et siffler la Marseillaise en même temps

attendons alors de voir son fichier et ses demandes précises

à+
Philippe

angelspeed · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Bonjour,

Je ne connais pas vos "habitudes". Si vous voulez un fichier exemple, il n'y a pas de problème. Je pensais que ce serait plus simple en mettant seulement le code vu qu'il n'est pas très long.

Je vous mets donc le fichier mais d'après ce que vous me dites il est pas possible d'utiliser le Worksheet_Change sur une cellule contenant une formule alors.

Dranreb · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Qu'est-ce qui a bien pu vous empêcher de penser à écrire la seconde moitié comme ça:

VB:

'Avec formule
If Not Application.Intersect(Target, Range("H:I")) Is Nothing Then
ActiveSheet.Unprotect
ligne = Target.Row

If Cells(ligne, 12).Value = "Terminée" Then
    Range(Cells(ligne, 8), Cells(ligne, 11)).Locked = True
    Range(Cells(ligne, 8), Cells(ligne, 11)).Font.Italic = True
Else
    Range(Cells(ligne, 8), Cells(ligne, 11)).Locked = False
    Range(Cells(ligne, 8), Cells(ligne, 11)).Font.Italic = False
End If

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect
End If

(ce que je n'aurais pas pu voir non plus sans le fichier)
À+

angelspeed · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Le fait que je ne connaisse pas assez vb certainement. En effet, suffisait de pas grand chose pour que cela fonctionne.
Merci beaucoup de votre aide.

J'aurais une dernière question pour finir cette partie en terme de confort d'utilisation : Existe-t-il un moyen pour que lorsque j'aille sur la feuille des commandes, la première ligne affichée sous le volet soit celle de la dernière/x ligne(s) verrouillée(s) ? je n'ai pas trouvé d'exemple pour cela.
(les intitulés des colonnes sont figées dans un volet - Lignes 1 à 3)

(Et je prends bien note de vos remarques pour mes prochains post)

Dranreb · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Au dernier "Terminée" en somme.
Ceci semble faire l'affaire:

VB:

Private Sub Worksheet_Activate()
Dim Plg As Range
Set Plg = Me.[L:L].Find(What:="Terminée", After:=Me.[L1], LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False)
If Not Plg Is Nothing Then ActiveWindow.ScrollRow = Plg.Row
End Sub

À+

angelspeed · 21 Septembre 2011

Re : VB - Verrouillages cellules avec case à cocher

Merci encore Dranreb cela fonctionne très bien.

En testant plus profondément j'ai remarqué une seule limite : la colonne ciblée ne doit pas être masquée pour que cela fonctionne.

Ce n'est pas le cas dans l'exemple que j'ai donné mais existe-t-il un moyen d'inclure les colonnes masquées dans cette méthode ?

Effectuez une recherche sur Excel Downloads...

VB - Verrouillages cellules avec case à cocher

angelspeed

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

phlaurent55

Nous a quittés en 2020

Pièces jointes

angelspeed

XLDnaute Junior

angelspeed

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

angelspeed

XLDnaute Junior

angelspeed

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

phlaurent55

Nous a quittés en 2020

angelspeed

XLDnaute Junior

Pièces jointes

Dranreb

XLDnaute Barbatruc

angelspeed

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

angelspeed

XLDnaute Junior

Discussions similaires

Nous accordons de l'importance à votre vie privée