Discussion :

[zaza45]

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 :

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

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 :

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

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

[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, …

[ZX12R]

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 :

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
'====================
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

[zaza45]

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 :

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

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 :

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

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

[ZX12R]

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 :

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
'========================================
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

[zaza45]

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 :

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
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

[ZX12R]

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+

[antonysansh]

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 :

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

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 :

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

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.

[ZX12R]

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

[zaza45]

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 :

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

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 :

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

 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 :

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

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

[antonysansh]

Si aucune formule dans ta feuille ça plante

Correction :

Code :

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

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 ^^

[zaza45]

Antony

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

Code :

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

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

[zaza45]

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 :

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
 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

[zaza45]

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 :

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
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