Verrouiller toutes les cellules sauf celles d'une certaine couleur

**borisp** · 30/05/2016, 11h24

Bonjour,

Je me tourne vers le forum car je souhaiterai vérouiller toutes les cellules de mon classeur qui contient plusieurs onglets sauf certaines cellules de deux couleurs.

Comme j'ai certaines cellules fusionnées, le code que j'utilisais bug. J'ai essayé avec celui ci-dessous mais j'ai le message d'erreur suivant : "wrong number of arguments or invalide property assignement" sur la ligne For Each Cel In Worksheets(y).Range

Avez-vous une solution sur ce code ou une solution différente pour ce problème?

Merci par avance et bonne semaine à tous.

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
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB
 
'dévérouiller toutes les feuilles
For i = 1 To Sheets.Count
Sheets(i).Unprotect Password:="xxx"
Next
 
 
 
 
 
For y = 1 To Worksheets.Count
Set Plage = Range("a1:EZ1000")
For Each Cel In Worksheets(y).Range
 
 
 
Mg = Cel.MergeArea.Address
TB = Split(Mg, ":")
Range(Mg).Locked = True
 
If Range(TB(0)).Interior.ColorIndex = 24 Or Range(TB(0)).Interior.ColorIndex = 36 Then
'MsgBox Sheets(y).Name & " " & n.Address
Cel.Locked = False
End If
Next
Next
 
        'vérouiller toutes les feuilles
For i = 1 To Sheets.Count
Sheets(i).Protect Password:="xxx"
 
Next
      End Sub

**casefayere** · 30/05/2016, 12h22

sur le principe, des corrections seront à faire car je ne possède qu'une feuille de ton fichier, mon exemple ne concerne que la structure du code

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
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB As String
Dim Sh As Worksheet
'déverrouiller toutes les feuilles
For Each Sh In ThisWorkbook.Worksheets
  Sh.Unprotect Password:="xxx"
  Set Plage = Sh.Range("a1:EZ1000")
  For Each Cel In Plage
    Mg = Cel.MergeArea.Address
    TB = Split(Mg, ":")
  Next Cel
  Sh.Range(Mg).Locked = True
  If Sh.Range(TB(0)).Interior.ColorIndex = 24 Or Sh.Range(TB(0)).Interior.ColorIndex = 36 Then
    'MsgBox Sheets(y).Name & " " & n.Address
    Sh.Range(TB(0)).Locked = False
  End If
  Sh.Protect Password:="xxx"
Next Sh
End Sub

Bien sur, fait à l'aveugle, attendons-nous à de nouveaux bugs

**borisp** · 30/05/2016, 13h56

Merci de ton aide.

L'erreur bloque sur cet ligne là :

Set Plage = Sh.Range("a1:EZ1000") avec comme message : object variable or with block variable not set

Désolé je ne maîtrise pas les déclarations de variables alors que je me rends bien compte que c'est la base.

Voici tout le code que j'ai utilisé si besoin :

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
Option Explicit
 
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB
Dim Sh As Worksheet
 
 
 
'dévérouiller classeur
ActiveWorkbook.Unprotect Password:="xxx"
Application.ScreenUpdating = 0
'dévérouiller toutes les feuilles
For Each Sh In ThisWorkbook.Worksheets
  Sh.Unprotect Password:="xxx"
 
Next
 
   Sheets("Paramètres").Visible = True
   Sheets("Charges_et_produits").Visible = True
         Sheets("E1_Tab_Donnees SA_BDD").Visible = True
            Sheets("E3_Affectation_SA_BDD").Visible = True
                  Sheets("E4_Ventilation_SA_BDD").Visible = True
                  Sheets("ListesSA").Visible = True
 
 
       Set Plage = Sh.Range("a1:EZ1000")
  For Each Cel In Plage
    Mg = Cel.MergeArea.Address
    TB = Split(Mg, ":")
  Next Cel
  Sh.Range(Mg).Locked = True
  If Sh.Range(TB(0)).Interior.ColorIndex = 24 Or Sh.Range(TB(0)).Interior.ColorIndex = 36 Then
    'MsgBox Sheets(y).Name & " " & n.Address
    Sh.Range(TB(0)).Locked = False
  End If
 
 
 
        'vérouiller toutes les feuilles
  Sh.Protect Password:="xx"
Next Sh
 
  Next
 
         Sheets("Paramètres").Visible = False
      Sheets("Charges_et_produits").Visible = False
         Sheets("E1_Tab_Donnees SA_BDD").Visible = False
            Sheets("E3_Affectation_SA_BDD").Visible = False
                  Sheets("E4_Ventilation_SA_BDD").Visible = False
                  Sheets("ListesSA").Visible = False
      'Vérouiller classeur
    ActiveWorkbook.Protect Password:="xxx", Structure:=True, Windows:=False
Application.ScreenUpdating = -1
 
End Sub

Merci

**casefayere** · 30/05/2016, 14h04

tu as complétement changé ton code sans tenir compte de beaucoup de mes propositions, regardes bien ce que tu fais

Set Plage = Sh.Range("a1:EZ1000") avec comme message : object variable or with block variable not set

Normal "Sh" en dehors d'une boucle,
et ça, tu es sur que tu ne peux pas faire autrement

Code :

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

1
2
3
4
5
6
   Sheets("Paramètres").Visible = True
   Sheets("Charges_et_produits").Visible = True
         Sheets("E1_Tab_Donnees SA_BDD").Visible = True
            Sheets("E3_Affectation_SA_BDD").Visible = True
                  Sheets("E4_Ventilation_SA_BDD").Visible = True
                  Sheets("ListesSA").Visible = True

déjà, pourquoi indenté quand c'est inutile, ensuite pourquoi pas une boucle, je te l'ai montré

**borisp** · 30/05/2016, 14h45

Le code tourne bien, plus de bug et j'ai remplacé les lignes que tu proposes par

Code :

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

Sh.Visible = True

c'est en effet plus propre.

Le problème est que cela ne fonctionne pas pour verrouiller toutes les cellules. Pour déverrouiller en fonction des deux couleurs c'est ok mais de verrouiller toutes les cellules non...

Est-ce que tu vois d'ou est le problème ?

Merci pour ta disponibilité

**casefayere** · 30/05/2016, 14h48

renvoies un code modifié et propre afin que je puisse vérifier là où ça pêche

**borisp** · 30/05/2016, 15h10

Le voici :

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
Option Explicit
 
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB
Dim Sh As Worksheet
 
 
 
'dévérouiller classeur
ActiveWorkbook.Unprotect Password:="xxx"
Application.ScreenUpdating = 0
'dévérouiller toutes les feuilles
For Each Sh In ThisWorkbook.Worksheets
  Sh.Unprotect Password:="xxx"
 
Sh.Visible = True
 
       Set Plage = Sh.Range("a1:EZ1000")
  For Each Cel In Plage
    Mg = Cel.MergeArea.Address
    TB = Split(Mg, ":")
  Next Cel
  Sh.Range(Mg).Locked = True
  If Sh.Range(TB(0)).Interior.ColorIndex = 24 Or Sh.Range(TB(0)).Interior.ColorIndex = 36 Then
    'MsgBox Sheets(y).Name & " " & n.Address
    Sh.Range(TB(0)).Locked = False
  End If
 
 
        'vérouiller toutes les feuilles
  Sh.Protect Password:="xxx"
Next Sh
 ' Next
 
 
      Sheets("Paramètres").Visible = False
      Sheets("Charges_et_produits").Visible = False
         Sheets("E1_Tab_Donnees SA_BDD").Visible = False
            Sheets("E3_Affectation_SA_BDD").Visible = False
                  Sheets("E4_Ventilation_SA_BDD").Visible = False
                  Sheets("ListesSA").Visible = False
      'Vérouiller classeur
    ActiveWorkbook.Protect Password:="xxx", Structure:=True, Windows:=False
Application.ScreenUpdating = -1
 
End Sub

**casefayere** · 30/05/2016, 15h25

Dans la procédure, je vois ça

Code :

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

1
2
3
4
5
6
7
8
9
'...
      Sheets("Paramètres").Visible = False
      Sheets("Charges_et_produits").Visible = False
      Sheets("E1_Tab_Donnees SA_BDD").Visible = False
      Sheets("E3_Affectation_SA_BDD").Visible = False
      Sheets("E4_Ventilation_SA_BDD").Visible = False
      Sheets("ListesSA").Visible = False
 
'....

dis-moi quelle feuille reste visible afin que j'améliore la lisibilité

**casefayere** · 30/05/2016, 16h17

en estimant que la feuille restant visible est celle-ci "E3_Affectation_CI&PD"

essayes ce code en tenant compte de mes indications à l'intérieur de celui-ci

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
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB
Dim Sh As Worksheet
 
'déprotéger classeur
ActiveWorkbook.Unprotect Password:="xxx"
Application.ScreenUpdating = 0
'déprotéger toutes les feuilles
For Each Sh In ThisWorkbook.Worksheets
  Sh.Unprotect Password:="xxx"
  Sh.Visible = True
  Sh.UsedRange.Locked = True'je verrouille au départ toutes les cellules
  Set Plage = Sh.Range("A1:EZ1000")'voir ta plage, il y a certainement mieux
  For Each Cel In Plage
    Mg = Cel.MergeArea.Address'ça, c'est ton histoire
    TB = Split(Mg, ":")
  Next Cel
  'Sh.Range(Mg).Locked = True 'ça plus besoin
  If Sh.Range(TB(0)).Interior.ColorIndex = 24 Or Sh.Range(TB(0)).Interior.ColorIndex = 36 Then
    Sh.Range(TB(0)).Locked = False
  End If
 'protéger toutes les feuilles
  Sh.Protect Password:="xxx"
  If Not Sh.Name = "E3_Affectation_CI&PD" Then Sh.Visible = False
Next Sh
'protéger classeur
ActiveWorkbook.Protect Password:="xxx", Structure:=True, Windows:=False
Application.ScreenUpdating = -1
 
End Sub

**borisp** · 30/05/2016, 16h57

Alors maintenant, ce code verrouille toutes les cellules, il ne prend plus en compte la commande sur les cellules colorées...

Pour ton autre remarque, il y a d'autres onglets à laisser visible c'est pour cela que je les ai laissé

**casefayere** · 30/05/2016, 17h04

oui avant je verrouille tout

Code :

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

Sh.UsedRange.Locked = True'je verrouille au départ toutes les cellules

ensuite, avec ces lignes je déverrouille les cellules correspondant aux critères

Code :

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

1
2
3
If Sh.Range(TB(0)).Interior.ColorIndex = 24 Or Sh.Range(TB(0)).Interior.ColorIndex = 36 Then
    Sh.Range(TB(0)).Locked = False
  End If

J'espère que tu reprends mes codes, tu les selectionnes entièrement et "Ctrl +C" enfin "Ctrl+v" dans l'éditeur

Pour ton autre remarque, il y a d'autres onglets à laisser visible c'est pour cela que je les ai laissé

ça ça sera facile à adapter

**borisp** · 30/05/2016, 17h19

Désolé mais cela ne fonctionne pas, j'ai revérifié en faisant comme tu le suggères un copier coller, mais les cellules colorées ne se déverrouillent plus..

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
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB
Dim Sh As Worksheet
 
'déprotéger classeur
ActiveWorkbook.Unprotect Password:="xxx"
Application.ScreenUpdating = 0
'déprotéger toutes les feuilles
For Each Sh In ThisWorkbook.Worksheets
  Sh.Unprotect Password:="xxx"
  Sh.Visible = True
  Sh.UsedRange.Locked = True 'je verrouille au départ toutes les cellules
  Set Plage = Sh.Range("a1:CZ500")
  For Each Cel In Plage
    Mg = Cel.MergeArea.Address
    TB = Split(Mg, ":")
  Next Cel
  If Sh.Range(TB(0)).Interior.ColorIndex = 24 Or Sh.Range(TB(0)).Interior.ColorIndex = 36 Then
    Sh.Range(TB(0)).Locked = False
  End If
 'protéger toutes les feuilles
  Sh.Protect Password:="xxx"
  Sheets("Paramètres").Visible = False
         Sheets("E1_Tab_Donnees SA_BDD").Visible = False
            Sheets("E3_Affectation_SA_BDD").Visible = False
                  Sheets("E4_Ventilation_SA_BDD").Visible = False
                  Sheets("ListesSA").Visible = False
Next Sh
'protéger classeur
ActiveWorkbook.Protect Password:="xxx", Structure:=True, Windows:=False
Application.ScreenUpdating = -1
 
End Sub

Après il y a peut être une solution plus simple que d'utiliser ça :

Code :

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

1
2
  Mg = Cel.MergeArea.Address
    TB = Split(Mg, ":")

Merci

**casefayere** · 30/05/2016, 17h26

(quand je vois ton code, tu n'as pas fait de copier/coller vu l'indentation......) je retire, j'ai vu pourquoi

au delà de ça, je crois avoir compris, je vais regarder

les cellules fusionnées ne sont pas conseillées, il y a d'autres moyens,il est vrai que si tu pouvais les éviter.....

**casefayere** · 30/05/2016, 17h31

quand je vois ton code, tu n'as pas fait de copier/coller vu l'indentation......

je retire, j'ai vu pourquoi

essayes ça, sans garantie pour les cellules fusionnées car je ne peux pas faire d'essai

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
Sub Lock_Cells()
 
Dim Cel As Range
Dim Plage As Range
Dim Mg As String, TB
Dim Sh As Worksheet
 
'déprotéger classeur
ActiveWorkbook.Unprotect Password:="xxx"
Application.ScreenUpdating = 0
'déprotéger toutes les feuilles
For Each Sh In ThisWorkbook.Worksheets
  Sh.Unprotect Password:="xxx"
  Sh.Visible = True
  Sh.UsedRange.Locked = True
  Set Plage = Sh.Range("a1:EZ1000")
  For Each Cel In Plage
    'Mg = Cel.MergeArea.Address
    'TB = Split(Mg, ":")
    If Cel.Interior.ColorIndex = 24 Or Cel.Interior.ColorIndex = 36 Then
      Cel.Locked = False
    End If
 
  Next Cel
 'protéger toutes les feuilles
  Sh.Protect Password:="xxx"
  If Not Sh.Name = "E3_Affectation_CI&PD" Then Sh.Visible = False
Next Sh
'protéger classeur
ActiveWorkbook.Protect Password:="xxx", Structure:=True, Windows:=False
Application.ScreenUpdating = -1
 
End Sub

**borisp** · 30/05/2016, 17h34

J'ai adapté le minimum nécessaire de ton message de 16H17.

Oui c'est sur que ce serait plus simple sans cellule fusionnée, mais ce n'est pas de ma volonté...

Merci de ton aide