MAC - Optimisation code VBA

Le 13/12/2015 à 23:42

Bonsoir à tous!

Tout est dans le titre...

Je cherche à optimiser ce code pour qu'il soit moins long et qu'il soit plus rapide à exécuter...

Si quelqu'un a 5 minutes à perdre !!! 

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C23").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J23:K23").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J23:K23").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C24").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J24:K24").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J24:K24").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C25").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J25:K25").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J25:K25").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C26").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J26:K26").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J26:K26").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C27").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J27:K27").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J27:K27").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C28").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J28:K28").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J28:K28").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C29").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J29:K29").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J29:K29").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C30").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J30:K30").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J30:K30").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C31").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J31:K31").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J31:K31").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C32").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J32:K32").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J32:K32").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C33").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J33:K33").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J33:K33").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

If Range("C34").Value = "FMC" Then

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J34:K34").Locked = True

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

Else

    ActiveSheet.Unprotect "MOT DE PASSE"

    Range("J34:K34").Locked = False

    ActiveSheet.Protect "MOT DE PASSE", _
    DrawingObjects:=True, _
    Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=True

    ActiveWindow.SmallScroll Down:=-100
    ActiveWindow.SmallScroll Down:=11

End If

End Sub
Le 14/12/2015 à 00:23

Bonjour

A vérifier si cela peut convenir

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Range("C23:C34"), Target) Is Nothing And Target.Count = 1 Then
    ActiveSheet.Unprotect "MOT DE PASSE"

    If Target.Value = "FMC" Then
      Range("J" & Target.Row).Resize(, 2).Locked = True
    Else
      Range("J" & Target.Row).Resize(, 2).Locked = False
    End If

    ActiveSheet.Protect "MOT DE PASSE", _
                        DrawingObjects:=True, _
                        Contents:=True, Scenarios:=True, _
                        UserInterfaceOnly:=True
  End If
End Sub
Le 14/12/2015 à 00:55

Tout simplement parfait !!!

Un grand MERCI à toi Banzai64

Rapidité et efficacité... Au top !!!

Encore Merci !

Rechercher des sujets similaires à "mac optimisation code vba"