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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastLig As Long
If Target.Count = 1 Then
Application.ScreenUpdating = False
LastLig = Cells(Rows.Count, 3).End(xlUp).Row
If Not Intersect(Target, Range("C4:V" & LastLig)) Is Nothing Then
If Target.Row = LastLig Then
Application.EnableEvents = False
Range("A" & Target.Row).Value = Target.Row - 3
If Range("AL" & Target.Row).Value = 0 Then
Range("X4:AK4").Copy Range("X" & LastLig)
Range("X" & LastLig & ":AK" & LastLig).Value = "non"
End If
Application.EnableEvents = True
End If
Modifie Target
ElseIf Not Intersect(Target, Range("X4:AK" & LastLig)) Is Nothing Then
Range("AL" & Target.Row).Value = Application.CountIf(Range("X" & LastLig & ":AK" & LastLig), "oui")
If Target.Value = "oui" Then
Transfert Target
Else
Supprime Target
End If
End If
End If
End Sub
'Sub de transfert
Private Sub Transfert(Targ As Range)
Dim LastLig As Long
With Sheets("Liste des produits par client")
.AutoFilterMode = False
LastLig = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Cells(LastLig, 1).Value = LastLig - 3
.Range("B" & LastLig & ":C" & LastLig).Value = Range("C" & Targ.Row & ":D" & Targ.Row).Value
.Range("D" & LastLig & ":F" & LastLig).Value = Range("G" & Targ.Row & ":I" & Targ.Row).Value
.Range("G" & LastLig).Value = Cells(3, Targ.Column).Value
.Range("M" & LastLig).Value = Range("A" & Targ.Row)
End With
End Sub
'Sub de modification
Private Sub Modifie(Targ As Range)
Dim Plage As Range
Dim Code As Long
Code = Cells(Targ.Row, 1).Value
With Sheets("Liste des produits par client")
.AutoFilterMode = False
Set Plage = .UsedRange
If Plage.Rows.Count > 1 Then
Plage.AutoFilter field:=13, Criteria1:=Code
If Plage.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Range("C" & Targ.Row & ":D" & Targ.Row).Copy Plage.Offset(1, 1).Resize(Plage.Rows.Count - 1, 2).SpecialCells(xlCellTypeVisible)
Range("G" & Targ.Row & ":I" & Targ.Row).Copy Plage.Offset(1, 3).Resize(Plage.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible)
End If
End If
Set Plage = Nothing
.AutoFilterMode = False
End With
End Sub
'Sub de suppression
Private Sub Supprime(Targ As Range)
Dim Plage As Range
Dim Prod As String
Dim Code As Long
Prod = Cells(3, Targ.Column).Value
Code = Cells(Targ.Row, 1).Value
With Sheets("Liste des produits par client")
.AutoFilterMode = False
Set Plage = .UsedRange
If Plage.Rows.Count > 1 Then
Plage.AutoFilter field:=13, Criteria1:=Code
Plage.AutoFilter field:=7, Criteria1:=Prod
If Plage.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
.Range("A4:A" & Plage.Rows.Count + 4).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
End If
Set Plage = Nothing
.AutoFilterMode = False
End With
End Sub |
Partager