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
| Dim i As Byte
Dim LIGNE As Byte
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERREUR
Application.ScreenUpdating = False
Application.EnableEvents = False
LIGNE = 6
For i = 1 To 5
ActiveSheet.Unprotect
If Range(Cells(LIGNE, 3), Cells(LIGNE, 3)).Value <> "" Then
Range(Cells(LIGNE, 5), Cells(LIGNE, 5)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="OUI,NON"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
End With
Selection.Locked = False
Else
Range(Cells(LIGNE, 5), Cells(LIGNE, 5)).Select
Selection.ClearContents
Selection.Validation.Delete
Selection.Locked = True
Selection.FormulaHidden = False
LIGNE = LIGNE + 2
End If
Next i
Call Protect1
Range("C6").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, AllowUsingPivotTables:=True
Application.ScreenUpdating = True
Application.EnableEvents = True
ERREUR:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, AllowUsingPivotTables:=True
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "Erreur", vbOKOnly
End Sub |
Partager