VBA Protect Sheet

[enzo_s]

Bonjour,

J'ai une macro qui tourne et qui met en du texte en filigrane mais quand je protégé la feuille cela ne fonctionne plus du coup j'ai ajouté le code ActiveSheet.Unprotectet ActiveSheet.protect

Le problème est quand la macro ce lance et prend du temps.
Y a il un moment plus simple pour le faire.

merci de l'aide

A+

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, b, i
a = Array("C11", "C12", "c14", "C15", "C17", "C18", "C19", "C20", "C21", "C22", "C23", "C24", "F11", "F16", "F18", "F19", "F20", "F21", "F22", "F23", "F24", "I11") 'à adapter
b = Array("XXXX", "XXXX", "xxxx@XXX.com", "Select", "Select", "Select", "Select", "Select", "Select", "Select", "Select", "XXXX", "Select", "Paste the MD5 result from the Switch", "XXXX", "10.X.1.1xx", "Select", "10.X.1.x", "40x", "10x" & "," & "20x" & "," & "30x", "10x,20x,30x,50x", "Select")        'à adapter
For i = 0 To UBound(a)
    b(i) = Replace(b(i), ",", Chr(130))
    If Range(a(i)) = "" Then
        ActiveSheet.Unprotect "xxx"
        Range(a(i)).Font.ColorIndex = 16
        Range(a(i)).Font.Bold = False 'non gras
        ActiveSheet.Protect "xxx"
       Range(a(i)) = b(i)
    ElseIf Range(a(i)) <> b(i) Then
        ActiveSheet.Unprotect "xxx"
        Range(a(i)).Font.ColorIndex = 1
        Range(a(i)).Font.Bold = True 'gras, facultatif
        ActiveSheet.Protect "xxx"
    End If
Next
 If Application.Intersect(Target, Range("F12:F13")) Is Nothing Then Exit Sub
Select Case Target.Address
    Case "$F$12"
        Application.EnableEvents = False
        Range("F13") = IIf(Target.Value = "Yes", "No", "Yes")
    Case "$F$13"
        Application.EnableEvents = False
        Range("F12") = IIf(Target.Value = "Yes", "No", "Yes")
End Select
Application.EnableEvents = True
End Sub

 

[Hasco]

Bonjour,

.UnProtect : juste avant votre boucle For (ou en debut de macro événementielle)

Et .Protect juste après (ou en fin de macro événementielle)

Bonne protection