Private Sub Worksheet_Change(ByVal Target As Range) et protection

**Giantrick** · 06/01/2013, 09h30

Bonjour,

Bonne année 2013 à vous tous.

Je pilote des actions via "Private Sub Worksheet_Change(ByVal Target As Range)" sur un onglet protégé en écriture qui doit agir sur des cellules verrouillées.

Dans cette macro, j'utilise plusieurs sous-macro.

Tout fonctionne correctement lorsque l'onglet n'est pas protégé.

J'ai un résultat différent pour chaque hypothèse suivante :

- Hypothèse I : je protège mon onglet, et n'inclus pas de code de déprotection dans ma macro "Private Sub Worksheet_Change(ByVal Target As Range)"

Résultat : la macro se bloque dès lors qu'elle doit modifier une cellule de l'onglet qui est verrouillée en écriture.
Constat : Je dirais que c'est logique car elle tente de modifier une cellule qui est protégée.

- Hypothèse II : je protège mon onglet, et inclus le code de déprotection au début et de reprotection à la fin.

Résultat : la déprotection s'exécute bien, mais le programme boucle sans fin.
Constat : Je dois avoir placé un bout de code quelque part qui n'est pas à la bonne place.

NB : ce que je ne m'explique pas, c'est qu'en dehors des soucis ci-dessus, j'ai déjà eu le cas, où mon programme n'incluant pas la déprotection et protection, s'exécutait sans erreur et sans boucle même si l'onglet était protégé ???

Voici le code pour analyse :

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wcCJ As Integer, wCVS As Integer, wCVD As Integer, wcVSE As Integer, wcVSC As Integer, ACTIVE_CELLULE As Range, wcZAA As String, wcZAB As String, wcZAC As String
Dim ZoneSourceVide As String, ZoneSource As String, ZoneDest As String
Dim wSh As Worksheet
Dim WSName As String, WsDes As String
Set ACTIVE_CELLULE = ActiveCell
 
    wcZAA = "$E$15:$E$25"
    wcZAB = "$E$27:$E$34"
    wcZAC = "$E$26"
 
    WSName = "FICHIER.xlsm"
    WsDes = "ONGLET1"
 
If Target.Count > 1 Then Exit Sub
'Workbooks(WSName).Worksheets(WsDes).Unprotect Password:="PASSWORD"
 
 
Set wSh = ThisWorkbook.Worksheets("ONGLET1")
With wSh
    ActiveWindow.DisplayZeros = False 'enlever les zéro en affichage
    wcVSC = Val(.Range("AA15").Value)
    wcVSE = Val(.Range("AA16").Value)
    wCVS = Val(.Range("AA18").Value)
    wCVD = Val(.Range("AA20").Value)
    wcCJ = Val(.Range("CD2").Value)
    ZoneSourceVide = "AO7:AQ7"
    ZoneSource = "AO3:AQ3"
    ZoneDest = "AO2:AO2"
 
    '1 = protection
    '2 = deprotection
If wCVD <> 1 Then
    Select Case wCVS
            Case 35 
            Call PROTECTION_CELLULE(wcZAA, 2)
            Call PROTECTION_CELLULE(wcZAC, 2)
            Call PROTECTION_CELLULE(wcZAB, 1)
            'blocker le bouton 
            Call CACHER_ENTITE(9)
            'deblocker le bouton 
            Call DECACHER_ENTITE(10)
            ACTIVE_CELLULE.Select
 
        Case 26 
             'deblocker le bouton 
            Call DECACHER_ENTITE(9)
            'blocker le bouton 
            Call CACHER_ENTITE(10)
            Call PROTECTION_CELLULE(wcZAA, 1)
            Call PROTECTION_CELLULE(wcZAC, 1)
           Selection.ClearContents
            Call PROTECTION_CELLULE(wcZAB, 2)
             ACTIVE_CELLULE.Select
 
     End Select
   End If
End With
Set Sh = Nothing
'Workbooks(WSName).Worksheets(WsDes).Protect Password:="PASSWORD", UserinterfaceOnly:=True
End Sub
 
Sub PROTECTION_CELLULE(ByVal ZoneName As String, ByVal i As Byte)
    '1 = protection
    '2 = deprotection
    Range(ZoneName).Select
   ' Dim WSName As String, WsDes As String
    If i = 1 Then
    Selection.Locked = True
    Selection.FormulaHidden = False
  '  MsgBox "la cellule " & ZoneName & "est protégée"
    ElseIf i = 2 Then
    Selection.Locked = False
    Selection.FormulaHidden = False
   ' MsgBox "la cellule " & ZoneName & "est déprotégée"
    End If
 End Sub
 
Sub CACHER_ENTITE(ByVal i As Byte)
 
Windows("FICHIER.xlsm").Activate
ActiveSheet.Shapes.Range(Array("Freeform " & i)).Visible = False
 
End Sub
Sub DECACHER_ENTITE(ByVal i As Byte)
'MsgBox "decacher - " & i
 
Windows("FICHIER.xlsm").Activate
ActiveSheet.Shapes.Range(Array("Freeform " & i)).Visible = True
 
End Sub

Merci de votre retour et bon dimanche à tous.

**Philippe Tulliez** · 06/01/2013, 11h27

Bonjour,

Résultat : la déprotection s'exécute bien, mais le programme boucle sans fin.

Tu parles de boucler, c'est à dire ?
Pas de message d'erreur ?

Je n'ai pas compris ce que tu cherches à faire dans cette procédures mais j'ai plusieurs remarques à faire sur une partie de ton code que je n'ai malheureusement pas déchiffré complètement d'où mon questionnement.

J'ai pu constater entre autre que tu masques les valeurs nulles or il existe nativement dans Excel une option qui fait cela et ce déjà dans la version 2003.
Tu vas dans les options avancées d'excel et dans la partie Afficher les options pour cette feuille de calcul, décocher l'option Afficher un zéro dans les cellules qui ont une valeur nulle Option que tu désactives si l'on entre un zéro dans une cellule de cette feuille alors que tu pourrais la désactiver une fois pour toute sans code VBA ou éventuellement si vraiment c'est indispensable de faire un code VBA pour cela à l'activation de la feuille.
Il serait intéressant de placer la commande Option Explicit en tête de module pour éviter les fautes orthographique sur le nom des variables comme par exemple wSh que tu déclares et que tu utilises en fin de procédure sans le w Set Sh = Nothing. Je te suggère d'ailleurs de cocher l'option Déclaration des variables obligatoire dans les outils du VBE ainsi tu ne devrais plus y penser.
Une autre chose m'a interloqué, tu assignes des valeurs aux variables

Code :

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

1
2
WSName = "FICHIER.xlsm"
WsDes = "ONGLET1"

et plus bas tu utilises des constantes en lieu et place des variables

Code :

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

Set wSh = ThisWorkbook.Worksheets("ONGLET1")

ou

Code :

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

Windows("FICHIER.xlsm").Activate

Tu utilises une variable objet pour la feuille. C'est très bien dans beaucoup de circonstance mais dans la procédure événementielle d'une feuille le mot clé ME se comportant comme une variable objet offre l'avantage de faire référence à l'instance de la classe dans laquelle le code est exécuté dans ce cas présent la feuille.

**mercatog** · 06/01/2013, 13h14

Attention à l'utilisation de l'évènement Change à l'intérieur duquel le code change une cellule (ou plage) X qui à son tour appel l'évènement Change...etc

Pour pallier à cela utilise

Code :

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

Application.EnableEvents = False

Avant tout changement de la cellule X et surtout ne pas oublier à la fin

Code :

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

Application.EnableEvents = True

Avec une gestion d'erreur rigoureuse.

PS: Si EnableEvents reste à false, l'évènement ne sera plus appelé.

**Giantrick** · 07/01/2013, 10h21

Merci à vous deux.

Philippe, j'ai corrigé les souci de nommage et affiner mon code pour être homogène sur les variables et constantes.

Pour l'affichage des zéros, je connais, mais je veux que l'option d'affichage des zéros soit automatiquement désactivé pour chaque action.

Concernant, la boucle du fichier, c'est parce que j'utilisais une cellule que je modifiait dans la macro évènementielle et comme le dit bien Mercatog, c'était la raison pour laquelle j'avais une boucle.

La solution est la suivante, j'ai changé ma condition comme dans le code ci-dessous :

Code :

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

If wCVD <> 1 Then

par

Code :

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

If Target.Address = "$E$14" Then

[E14] étant la cellule qui est à modifier pour éxécuter la macro évènementielle
et en plus j'ai ajouter une condition de END en bas de macro

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wcCJ As Integer, wCVS As Integer, wCVD As Integer, wcVSE As Integer, wcVSC As Integer, ACTIVE_CELLULE As Range, wcZAA As String, wcZAB As String, wcZAC As String
Dim ZoneSourceVide As String, ZoneSource As String, ZoneDest As String
Dim wSh As Worksheet
Dim WSName As String, WsDes As String
Set ACTIVE_CELLULE = ActiveCell
 
    wcZAA = "$E$15:$E$25"
    wcZAB = "$E$27:$E$34"
    wcZAC = "$E$26"
 
    WSName = "FICHIER.xlsm"
    WsDes = "ONGLET1"
 
If Target.Count > 1 Then Exit Sub
'Workbooks(WSName).Worksheets(WsDes).Unprotect Password:="PASSWORD"
 
 
Set wSh = ThisWorkbook.Worksheets("ONGLET1")
With wSh
    ActiveWindow.DisplayZeros = False 'enlever les zéro en affichage
    wcVSC = Val(.Range("AA15").Value)
    wcVSE = Val(.Range("AA16").Value)
    wCVS = Val(.Range("AA18").Value)
    wCVD = Val(.Range("AA20").Value)
    wcCJ = Val(.Range("CD2").Value)
    ZoneSourceVide = "AO7:AQ7"
    ZoneSource = "AO3:AQ3"
    ZoneDest = "AO2:AO2"
 
    '1 = protection
    '2 = deprotection
If Target.Address = "$E$14" Then
    Select Case wCVS
            Case 35 
            Call PROTECTION_CELLULE(wcZAA, 2)
            Call PROTECTION_CELLULE(wcZAC, 2)
            Call PROTECTION_CELLULE(wcZAB, 1)
            'blocker le bouton 
            Call CACHER_ENTITE(9)
            'deblocker le bouton 
            Call DECACHER_ENTITE(10)
            ACTIVE_CELLULE.Select
 
        Case 26 
             'deblocker le bouton 
            Call DECACHER_ENTITE(9)
            'blocker le bouton 
            Call CACHER_ENTITE(10)
            Call PROTECTION_CELLULE(wcZAA, 1)
            Call PROTECTION_CELLULE(wcZAC, 1)
           Selection.ClearContents
            Call PROTECTION_CELLULE(wcZAB, 2)
             ACTIVE_CELLULE.Select
     End Select
     Else
     Workbooks(WSName).Worksheets(WsDes).Protect Password:="PASSWORD", UserinterfaceOnly:=True
     End
   End If
End With
Set Sh = Nothing
'Workbooks(WSName).Worksheets(WsDes).Protect Password:="PASSWORD", UserinterfaceOnly:=True
End Sub
 
Sub PROTECTION_CELLULE(ByVal ZoneName As String, ByVal i As Byte)
    '1 = protection
    '2 = deprotection
    Range(ZoneName).Select
   ' Dim WSName As String, WsDes As String
    If i = 1 Then
    Selection.Locked = True
    Selection.FormulaHidden = False
  '  MsgBox "la cellule " & ZoneName & "est protégée"
    ElseIf i = 2 Then
    Selection.Locked = False
    Selection.FormulaHidden = False
   ' MsgBox "la cellule " & ZoneName & "est déprotégée"
    End If
 End Sub
 
Sub CACHER_ENTITE(ByVal i As Byte)
 
Windows("FICHIER.xlsm").Activate
ActiveSheet.Shapes.Range(Array("Freeform " & i)).Visible = False
 
End Sub
Sub DECACHER_ENTITE(ByVal i As Byte)
'MsgBox "decacher - " & i
 
Windows("FICHIER.xlsm").Activate
ActiveSheet.Shapes.Range(Array("Freeform " & i)).Visible = True
 
End Sub

Et là je n'ai plus de boucle et tout s'exécute correctement.

Bonne journée

Private Sub Worksheet_Change(ByVal Target As Range) et protection [Toutes versions]

Macros et VBA Excel

Discussions similaires

Partager

Partager