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
| Private Sub Worksheet_Change(ByVal Target As Range)
'Déclaration ======================================
Dim F As Worksheet
Dim Plg As Range, Cel As Range, Cel_1 As Range
Dim Lig As Long
Dim Flg As Boolean
Dim Txt_Msg As String
'MEI ==============================================
If Intersect(Target, Columns("C")) Is Nothing Then Exit Sub
Set Plg = Intersect(Target, Columns("C"))
'Boucle de Traitement =============================
For Each Cel In Plg
'Validité du traitement -----------------------
If IsDate(Cells(Cel.Row, "C")) Then
'Recherche feuille ------------------------
Flg = True
For Each F In Sheets
If UCase(Mid(F.Name, 7, 4)) Like UCase(Left(Format(Cel, "mmmm"), 4)) Then
Flg = False
Exit For
End If
Next F
'Feuille non-trouvée ----------------------
If Flg Then
MsgBox Chr(13) & "La feuille ""Achat " & Format(Cel, "mmmm") & _
""" n'existe pas ou est mal orthographiée(Voir liste L16)!" & _
"VEUILLEZ LA CRÉER AVANT", vbExclamation + vbOKOnly, _
"-- FEUILLE ABSENTE --"
Exit Sub
End If
'MAJ --------------------------------------
Flg = True
For Lig = 2 To F.[A65536].End(xlUp).Row
If Cel.Offset(0, -2) = F.Cells(Lig, "A") And _
Cel.Offset(0, -1) = F.Cells(Lig, "B") And _
Cel.Offset(0, 4) = F.Cells(Lig, "E") Then
F.Cells(Lig, "C") = Cel
Flg = False
Exit For
End If
Next Lig
'Création ----------------------------------
If Flg Then
Rows(Cel.Row).Copy F.Rows(Lig)
F.Range(F.Cells(Lig, "E"), F.Cells(Lig, "F")).Delete Shift:=xlToLeft
End If
Txt_Msg = Txt_Msg & Chr(13) & F.Name
End If
Next Cel
If Txt_Msg <> "" Then MsgBox "Mise à jour de(s) page(s) :" & _
Txt_Msg, vbOKOnly, "COPIE VALEURS"
End Sub |
Partager