Macro pour portéger seulement des formules et non les cellules vides

Version imprimable

Bonjour

Je patauge complétement. j'aimerai utiliser une macro qui pour toutes les feuilles d'un classeur, verrouille les cellules contenant des formules et laisse déverrouillées les autres cellules (vides ou non).
J'utilise cette macro mais elle bloque aussi les cellules vides.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
'protéger_cellules_formules()
 
    Dim ws1 As Worksheet
    For Each ws1 In ThisWorkbook.Worksheets
 
          With ws1.UsedRange
 
            With .Cells.SpecialCells(xlCellTypeConstants, 23)
                .Locked = False
                .FormulaHidden = False
            End With
 
 
        End With

si je rajoute le code suivant j'ai un code erreur 1004

Code:

1
2
3
4
5
6
7
'deverouillage des cellules vides
            'If cell.Value = "" Then cell.Locked = False
 
            'With .Cells.SpecialCells(xlCellTypeBlanks)
                '.Locked = False
                '.FormulaHidden = False
            'End With

Quelqu'un aurait-il une idée

Merci beaucoup

11/02/2016, 16h47
Marc-L

Bonjour,

utiliser la propriété HasFormula ou encore la méthode SpecialCells avec le bon paramètre (cf l'aide VBA !) …

______________________________________________________________________________________________________
Je suis Paris, Charlie, …

protection des formules uniquement

Bonjour,

avec le code suivant, ca à l'air de le faire sur une feuille. Complète le code pour le faire sur ttes les feuilles...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
'====================
Option Explicit
 
Sub ProtegeFormules()
'Selectionne ttes celllules de feuille active et dévérouille la protection
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
 
'Sélectionne ttes les cellules contenant une formule et revérouille la protection
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
 
'Active la protection de la feuille avec passe "bibi"
    ActiveSheet.Protect "bibi"
 
End Sub
 
'=======================

Cordialement

Bruno Lemaire

Macro pour portéger seulement des formules et non les cellules vide

Bonjour Bruno

Merci beaucoup pour ta réponse. Ta macro fonctionne bien sur une seule feuille de mon fichier.

Mais quand je l’intègre dans la macro pour l'ensemble des feuilles, j'ai toujours des cellules vides qui n'ont jamais reçu de valeurs qui restent bloquées et la ligne With .SpecialCells(xlCellTypeFormulas, 23) se bloque en jaune.

Le début de la macro déverrouille bien toutes les cellules
Code:

1 2 3 4 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Next
J'aimerai qu'en une seule fois toutes les cellules de toutes les feuilles du classeur qui ont seulement des formules soit verrouillées

Comme je ne connais pas trop le VBA quelque chose dans la logique m’échappe
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Dim ws1 As Worksheet For Each ws1 In ThisWorkbook.Worksheets With ws1.UsedRange With .SpecialCells(xlCellTypeFormulas, 23) .Locked = True .FormulaHidden = False End With End With ws1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Next

suite protection formules

Bonjour madame Zaza,

Le problème vient du fait que le pointeur reste tjs sur la même feuille. et donc la macro cherche à remodifier les propriétés de cellules alors que la feuille a déjà été protégée.

Modifie ton code sur ce modèle qui tourne :

Code:

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
'========================================
Option Explicit
 
Sub ProtegeFormules()
'Selectionne ttes celllules de feuille active et dévérouille la protection cellule
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
 
'Sélectionne ttes les cellules contenant une formule et revérouille la protection
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
 
'Active la protection de la feuille avec passe "bibi"
    ActiveSheet.Protect "bibi"
 
End Sub
 
'*************************************************
Sub ProtFormClass() 'Active la protection de ttes les feuilles
Dim Sh As Worksheet
 
For Each Sh In ActiveWorkbook.Sheets
    Sh.Activate
    ProtegeFormules
 
Next
End Sub
 
'*************************************************
Sub Depr_Class() 'Ote la protection de ttes les feuilles
Dim Sh As Worksheet
 
For Each Sh In ActiveWorkbook.Sheets
    Sh.Activate
    ActiveSheet.Unprotect "bibi"
 
Next
 
End Sub
 
'=========================

Voilà, tu aurai exécuté ton code en mode pas à pas, je pense que tu aurai décelé le problème...

BIZ à ZAZA, si je peux me permettre ;)

J'ai essayé ton code mais comme il y a beaucoup de feuilles, c'est très long.

J'ai modifié selon ta remarque le code de départ, je n'ai plus l'erreur et en testant plusieurs feuilles j'ai bien mes formules protégées mais les cellules vides qui n'ont jamais eu de valeur restent verrouillées aussi.

j'ai oublié de mettre le code corrigé; Je ne sais pas si c'est juste mais je n'ai pas d'erreur
Code:

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 Sub protéger_cellules_formules() Dim ws1 As Worksheet For Each ws1 In ThisWorkbook.Worksheets ActiveSheet.Unprotect With ws1.UsedRange With Selection.SpecialCells(xlCellTypeFormulas, 23).Select Selection.Locked = True Selection.FormulaHidden = False End With End With ws1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Next ActiveSheet.Protect ActiveWorkbook.Protect MsgBox "fin" End Sub

12/02/2016, 15h17
ZX12R

suite

déjà un truc m'étonne dans ta boucle de parcours feuille je ne vois pas d'instruction du type Ws.activate pour pointer la feuille à traiter.

Et perso j'ai testé sur trois feuille contenant des formules. A l'issue d'exécution les cellules vides sont modifiables.

Persévère !

A+

Bonjour à tous,

Les Select, Selection, Activate sont à proscrire !
Ils ne font que ralentir le code inutilement

Ce code est suffisant pour verrouille toutes les cellules avec formule de toutes les feuilles du classeur.
Code:

1 2 3 4 5 6 7 8 Sub VerrouillerFormule() Dim sh As Worksheet For Each sh In ThisWorkbook.Sheets sh.Cells.Locked = False sh.Cells.SpecialCells(xlCellTypeFormulas).Locked = True sh.Protect Password:="mdp", UserInterfaceOnly:=True Next End Sub
Par contre si une formule est mise dans une cellule non protégée, elle ne va pas se verrouiller pour autant.

Tu peux ajouter ça dans le module ThisWorkbook :
Code:

1 2 3 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.HasFormula Then Target.Locked = True End Sub
A chaque modification de cellule dans ton classeur, si la saisie est une formule alors elle se verrouiller automatiquement.

12/02/2016, 15h54
ZX12R

suite

Bonjour Antony,

Je suis bien d'accord avec toi ca ralentit forcément. Mais il semblerait que ton niveau d'utilisation VBA Excel soit un peu supérieur à notre amie... ;)

Je préfère lui indiquer une solution qui lui permet de voir en mode pas à pas ce qui se passe et donc pouvoir plus facilement déboguer.

Après débogage, un blocage des rafraîchissements écran avec "application.screenupdating=false" fera peut être son affaire...

Bien cordialement.

Bruno
15/02/2016, 12h53
zaza45

Macro pour portéger seulement des formules et non les cellules vides
Bonjour à tous

Merci Antony pour ton code qui va à merveille sur certains de mes fichiers. C'est très rapide.

Mais sur certains fichiers il y a un problème
ligne en jaune sur le code

Code:

sh.Cells.SpecialCells(xlCellTypeFormulas).Locked = True

Le verrouillage des formules se fait bien sur chaque feuilles et je n'ai plus le problème avec les cellules vides bloquées mais la macro ne se termine pas.

Si j'adapte le code de Bruno il bloque sur

Code:

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

alors que j'ai passé la macro de dévérouillage de toutes les feuilles avant et qu'il se termine bien
Code:

1 2 3 4 5 6 7 8 Sub deproteger_feuilles() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Next MsgBox "fin" End Sub
Effectivement je ne connais pas du tout VBA et je ne sais pas comment on fait du pas à pas.

De plus quelle est la signification "d'option explicite"

merci beaucoup pour votre aide

Si aucune formule dans ta feuille ça plante

Correction :
Code:

1 2 3 4 5 6 7 8 9 10 Sub VerrouillerFormule() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets Sh.Cells.Locked = False On Error Resume Next Set Sh = Sh.Cells.SpecialCells(xlCellTypeFormulas).Locked = True On Error GoTo 0 Sh.Protect Password:="mdp", UserInterfaceOnly:=True Next End Sub
Option Explicit ne sert à rien dans ton cas.
C'est une indication en tête de module pour obliger la déclaration de toutes les variables qui vont être utilisées.

Il est conseillé de le laisser pour ne pas laisser Excel faire n'importe quoi ^^

Macro pour portéger seulement des formules et non les cellules vide

Antony

Le nouveau code ne protège aucune formule. La macro ne tourne pas

Code:

1
2
3
4
5
6
7
8
9
10
11
Sub VerrouillerFormule()
    Dim Sh As Worksheet
        For Each Sh In ThisWorkbook.Sheets
            Sh.Cells.Locked = False
            'On Error Resume Next
                Set Sh = Sh.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
            'On Error GoTo 0
            Sh.Protect
        Next
 
 MsgBox "fin"

Peux-tu m'expliquer comment faire du pas à pas pour tester et que signifie UserInterfaceOnly:=True

Merci beaucoup

Macro pour portéger seulement des formules et non les cellules vide

Antony

Il se peut que ton ton code ne fonctionne pas car la dernière feuille du classeur est un TCD qui 's'appelle "pilotage" donc il n'y a pas de formules.

C'est peut-être cela le problème. Pourtant J'aimerai bien utiliser ta solution qui semble beaucoup plus rapide

Pour l'instant, après maintes relance, je lance le déverrouillage des formules en début de macro puis à la fin je lance ce code qui fonctionne bien mais qui est très long
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Dim sh As Worksheet For Each sh In ActiveWorkbook.Sheets sh.Activate 'ProtegeFormules() 'Selectionne ttes celllules de feuille active et dévérouille la protection cellule Cells.Select Selection.Locked = False Selection.FormulaHidden = False 'Sélectionne ttes les cellules contenant une formule et revérouille la protection On Error Resume Next Selection.SpecialCells(xlCellTypeFormulas, 23).Select Selection.Locked = True Selection.FormulaHidden = False On Error GoTo 0 'Active la protection de la feuille avec passe "bibi" ActiveSheet.Protect "bibi" Next

Macro pour protéger seulement des formules et non des cellules vides

Bonjour à tous

J'ai fini par faire ce code qui est moins long mais qui fonctionne même avec des feuilles dans le classeur sans formules.

Mais quelqu'un a-t-il une idée pour que la macro soit plus rapide

Code:

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
Sub Verrouillage_de_formules()
 
MsgBox "Attendre le message fin"
 
'Bloque l'affichage de l'écran pendant la macro
    Application.ScreenUpdating = False
 
 
Dim Sh As Worksheet
 
    For Each Sh In ActiveWorkbook.Sheets
        Sh.Activate
 
        'ProtegeFormules()
        'Selectionne ttes celllules de feuille active et dévérouille la protection cellule
 
        Sh.Cells.Locked = False
        Sh.Cells.FormulaHidden = False
 
        'Sélectionne ttes les cellules contenant une formule et revérouille la protection
            On Error Resume Next
 
            Selection.SpecialCells(xlCellTypeFormulas, 23).Locked = True
            Sh.Cells.FormulaHidden = False
 
            On Error GoTo 0
 
        'Active la protection de la feuille avec passe "bibi"
 
        Sh.Protect Password:="bibi"
 
   Next
 
   Sheets("pilotage").Unprotect "bibi"
 
   'Débloque l'affichage de l'écran pendant la macro
    Application.ScreenUpdating = True
 
    MsgBox "fin"
 
End Sub