Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Limite sur le nombre de macro évenementielles pour un e même feuille ...

6 réponses
Avatar
François
Bonjour à tous,

dans une feuille, j'ai une bonne douzaine de macros évenementielles sous
Worksheet_Change et deux sous Worksheet_SelectionChange.

En en rajoutant dans Worksheet_Change, j'ai eu un bug avec comme message :
"Erreur de compilation" " Procédure trop grande" !

Y-a-t-il une limitation au nombre de ces macros ?
Y-a-t-il un moyen de contourner cela ?

Je mets ci-dessous les macros, quoique je ne soit pas sûr que cela soit
utile.
(si nécessaire, je pourrais mettre le fichier en Cjoint)

Merci à tous pour vos suggestions

François

Dim temoin As Boolean
Dim debut As Date, fin As Date
Dim m As Byte, n As Byte
Private Sub Worksheet_Change(ByVal Target As Range)

' pour les changements de dates des blocs A17:A21 jusqu'à A87:A91 ->
changement des cellules connexes
If Not Intersect(Target, Range("A17:A21")) Is Nothing And Target.Count =
1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And [A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A31:A35")) Is Nothing And Target.Count =
1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And [A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A45:A49")) Is Nothing And Target.Count =
1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And [A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A59:A63")) Is Nothing And Target.Count =
1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And [A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A73:A77")) Is Nothing And Target.Count =
1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And [A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A87:A91")) Is Nothing And Target.Count =
1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And [A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changement du nom du client en AJ1 -> changement de toutes
les cellules connexes
If Not Intersect(Target, Range("AJ1")) Is Nothing And Target.Count = 1
And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And [A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And [A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And [A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And [A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And [A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And [A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changements de pré/postfixes des blocs AJ13:AJ14 jusqu'à
AJ83:AJ84 -> changement de toutes les cellules connexes
If Not Intersect(Target, Range("AJ13:AJ14")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And [A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If


If Not Intersect(Target, Range("AJ27:AJ28")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And [A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ41:AJ42")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And [A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ55:AJ56")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And [A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ69:AJ70")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And [A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ83:AJ84")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And [A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If

' pour le classement alphabétique des préfixes de la colonne AK
If Not Intersect(Target, Range("AK2:AK1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AK2", [AK1000].End(xlUp)).Sort _
key1:=Range("AK2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabétique des postfixes de la colonne AL
If Not Intersect(Target, Rang

6 réponses

Avatar
Daniel.C
Bonsoir.
Tu tournes avec quelle version ?
Avec 2007, je n'ai drooit qu'à une procédure portant le même nom.
Cordialement.
Daniel
"François" a écrit dans le message de news:


Bonjour à tous,

dans une feuille, j'ai une bonne douzaine de macros évenementielles sous
Worksheet_Change et deux sous Worksheet_SelectionChange.

En en rajoutant dans Worksheet_Change, j'ai eu un bug avec comme message :
"Erreur de compilation" " Procédure trop grande" !

Y-a-t-il une limitation au nombre de ces macros ?
Y-a-t-il un moyen de contourner cela ?

Je mets ci-dessous les macros, quoique je ne soit pas sûr que cela soit
utile.
(si nécessaire, je pourrais mettre le fichier en Cjoint)

Merci à tous pour vos suggestions

François

Dim temoin As Boolean
Dim debut As Date, fin As Date
Dim m As Byte, n As Byte
Private Sub Worksheet_Change(ByVal Target As Range)

' pour les changements de dates des blocs A17:A21 jusqu'à A87:A91 ->
changement des cellules connexes
If Not Intersect(Target, Range("A17:A21")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A31:A35")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A45:A49")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A59:A63")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A73:A77")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A87:A91")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changement du nom du client en AJ1 -> changement de toutes
les cellules connexes
If Not Intersect(Target, Range("AJ1")) Is Nothing And Target.Count = 1
And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changements de pré/postfixes des blocs AJ13:AJ14 jusqu'à
AJ83:AJ84 -> changement de toutes les cellules connexes
If Not Intersect(Target, Range("AJ13:AJ14")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If


If Not Intersect(Target, Range("AJ27:AJ28")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ41:AJ42")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ55:AJ56")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ69:AJ70")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ83:AJ84")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If

' pour le classement alphabétique des préfixes de la colonne AK
If Not Intersect(Target, Range("AK2:AK1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AK2", [AK1000].End(xlUp)).Sort _
key1:=Range("AK2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabétique des postfixes de la colonne AL
If Not Intersect(Target, Range("AL2:AL1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AL2", [AL1000].End(xlUp)).Sort _
key1:=Range("AL2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du nombre de participants de la colonne
AM
If Not Intersect(Target, Range("AM2:AM1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AM2", [AM1000].End(xlUp)).Sort _
key1:=Range("AM2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du prix/kilomètre de la colonne AN
If Not Intersect(Target, Range("AN2:AN1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AN2", [AN1000].End(xlUp)).Sort _
key1:=Range("AN2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabètique des noms et adresses des colonnes
AO -> AS
If Not Intersect(Target, Range("AO2:AO1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AO2", [AO1000].End(xlUp).Offset(0, 4)).Sort _
key1:=Range("AO2"), order1:=xlAscending
temoin = False
End If




' pour afficher les jours décomptés sur la feuille Fact
If Not Intersect(Target, Range("A17:A91")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
debut = Application.WorksheetFunction.Max(Range("A17:A91"))
fin = 0
For m = 0 To 83 Step 14
For n = 0 To 4
With Cells(m + n + 17, 1)
If .Value <> "" And Weekday(.Value) > 1 And
Weekday(.Value) < 7 Then
If .Value < debut Then
debut = .Value
End If
If .Value > fin Then
fin = .Value
End If
End If
End With
Next n
Next m
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yyyy")
ElseIf debut = fin Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du " & Format(fin, "d
mmmm yyyy")
Else
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du "
End If
temoin = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

ActiveSheet.Calculate
If Not Intersect([AJ13:AJ86], Target) Is Nothing And Target.Count = 1
Then
For n = 0 To 2
If Target.Row = 13 + n Or Target.Row = 27 + n Or Target.Row =
41 + n Or Target.Row = 55 + n Or Target.Row = 69 + n Or Target.Row = 83 +
n Then
SendKeys "%{DOWN}"
End If
Next n
End If
If Not Intersect([AJ1:AJ2], Target) Is Nothing And Target.Count = 1
Then
SendKeys "%{DOWN}"
End If

h = ActiveCell.Height
w2 = ActiveCell.Width
t = ActiveCell.Top
w = ActiveCell.Left

ActiveSheet.Unprotect
'Teste si les rectangles existent déjà.
On Error Resume Next
ActiveSheet.Shapes("RectangleV").Delete
On Error Resume Next
ActiveSheet.Shapes("RectangleH").Delete
On Error GoTo 0

'Ajoute les rectangles
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, t, w, h).Name =
"RectangleV"
With ActiveSheet.Shapes("RectangleV")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Shapes.AddShape(msoShapeRectangle, w, 0, w2, t).Name =
"RectangleH"

With ActiveSheet.Shapes("RectangleH")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Protect , True, True, True

End Sub





Avatar
MichDenis
Voici un bout de texte qui commente le poids que peut avoir
le code d'un module... malheureusement, je n'ai plu l'auteur
de ces lignes ...probablement, cette limite ne s'adresse pas
à la version Excel 2007

********************
"VBA has an undocumented "soft limit" on the maximum size of any single
standard code module. A standard code moldule should not exceed 64 KB as
measured by its text file size when exported from the project.Your
project will not crash immediately upon a single module exceeding this
64KB limit, but consistently exceeding this limit will almost invariably
lead to an unstable application."
********************

Et 64 Kb, c'est déjà beaucoup !





"François" a écrit dans le message de news:


Bonjour à tous,

dans une feuille, j'ai une bonne douzaine de macros évenementielles sous
Worksheet_Change et deux sous Worksheet_SelectionChange.

En en rajoutant dans Worksheet_Change, j'ai eu un bug avec comme message :
"Erreur de compilation" " Procédure trop grande" !

Y-a-t-il une limitation au nombre de ces macros ?
Y-a-t-il un moyen de contourner cela ?

Je mets ci-dessous les macros, quoique je ne soit pas sûr que cela soit
utile.
(si nécessaire, je pourrais mettre le fichier en Cjoint)

Merci à tous pour vos suggestions

François

Dim temoin As Boolean
Dim debut As Date, fin As Date
Dim m As Byte, n As Byte
Private Sub Worksheet_Change(ByVal Target As Range)

' pour les changements de dates des blocs A17:A21 jusqu'à A87:A91 ->
changement des cellules connexes
If Not Intersect(Target, Range("A17:A21")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And [A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A31:A35")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And [A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A45:A49")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And [A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A59:A63")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And [A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A73:A77")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And [A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A87:A91")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And [A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changement du nom du client en AJ1 -> changement de toutes
les cellules connexes
If Not Intersect(Target, Range("AJ1")) Is Nothing And Target.Count = 1
And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And [A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And [A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And [A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And [A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And [A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And [A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changements de pré/postfixes des blocs AJ13:AJ14 jusqu'à
AJ83:AJ84 -> changement de toutes les cellules connexes
If Not Intersect(Target, Range("AJ13:AJ14")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And [A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If


If Not Intersect(Target, Range("AJ27:AJ28")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And [A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ41:AJ42")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And [A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ55:AJ56")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And [A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ69:AJ70")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And [A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ83:AJ84")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And [A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If

' pour le classement alphabétique des préfixes de la colonne AK
If Not Intersect(Target, Range("AK2:AK1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AK2", [AK1000].End(xlUp)).Sort _
key1:=Range("AK2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabétique des postfixes de la colonne AL
If Not Intersect(Target, Range("AL2:AL1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AL2", [AL1000].End(xlUp)).Sort _
key1:=Range("AL2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du nombre de participants de la colonne
AM
If Not Intersect(Target, Range("AM2:AM1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AM2", [AM1000].End(xlUp)).Sort _
key1:=Range("AM2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du prix/kilomètre de la colonne AN
If Not Intersect(Target, Range("AN2:AN1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AN2", [AN1000].End(xlUp)).Sort _
key1:=Range("AN2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabètique des noms et adresses des colonnes
AO -> AS
If Not Intersect(Target, Range("AO2:AO1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AO2", [AO1000].End(xlUp).Offset(0, 4)).Sort _
key1:=Range("AO2"), order1:=xlAscending
temoin = False
End If




' pour afficher les jours décomptés sur la feuille Fact
If Not Intersect(Target, Range("A17:A91")) Is Nothing And Target.Count 1 And Not temoin Then
temoin = True
debut = Application.WorksheetFunction.Max(Range("A17:A91"))
fin = 0
For m = 0 To 83 Step 14
For n = 0 To 4
With Cells(m + n + 17, 1)
If .Value <> "" And Weekday(.Value) > 1 And
Weekday(.Value) < 7 Then
If .Value < debut Then
debut = .Value
End If
If .Value > fin Then
fin = .Value
End If
End If
End With
Next n
Next m
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yyyy")
ElseIf debut = fin Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du " & Format(fin, "d
mmmm yyyy")
Else
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du "
End If
temoin = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

ActiveSheet.Calculate
If Not Intersect([AJ13:AJ86], Target) Is Nothing And Target.Count = 1
Then
For n = 0 To 2
If Target.Row = 13 + n Or Target.Row = 27 + n Or Target.Row = 41
+ n Or Target.Row = 55 + n Or Target.Row = 69 + n Or Target.Row = 83 + n
Then
SendKeys "%{DOWN}"
End If
Next n
End If
If Not Intersect([AJ1:AJ2], Target) Is Nothing And Target.Count = 1 Then
SendKeys "%{DOWN}"
End If

h = ActiveCell.Height
w2 = ActiveCell.Width
t = ActiveCell.Top
w = ActiveCell.Left

ActiveSheet.Unprotect
'Teste si les rectangles existent déjà.
On Error Resume Next
ActiveSheet.Shapes("RectangleV").Delete
On Error Resume Next
ActiveSheet.Shapes("RectangleH").Delete
On Error GoTo 0

'Ajoute les rectangles
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, t, w, h).Name "RectangleV"
With ActiveSheet.Shapes("RectangleV")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Shapes.AddShape(msoShapeRectangle, w, 0, w2, t).Name "RectangleH"

With ActiveSheet.Shapes("RectangleH")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Protect , True, True, True

End Sub
Avatar
François
Bonjour Daniel C.

je suis sur excel 2000 sous XP HE

merci

François

"Daniel.C" a écrit dans le message de news:

Bonsoir.
Tu tournes avec quelle version ?
Avec 2007, je n'ai drooit qu'à une procédure portant le même nom.
Cordialement.
Daniel
"François" a écrit dans le message de news:


Bonjour à tous,

dans une feuille, j'ai une bonne douzaine de macros évenementielles sous
Worksheet_Change et deux sous Worksheet_SelectionChange.

En en rajoutant dans Worksheet_Change, j'ai eu un bug avec comme message
:
"Erreur de compilation" " Procédure trop grande" !

Y-a-t-il une limitation au nombre de ces macros ?
Y-a-t-il un moyen de contourner cela ?

Je mets ci-dessous les macros, quoique je ne soit pas sûr que cela soit
utile.
(si nécessaire, je pourrais mettre le fichier en Cjoint)

Merci à tous pour vos suggestions

François

Dim temoin As Boolean
Dim debut As Date, fin As Date
Dim m As Byte, n As Byte
Private Sub Worksheet_Change(ByVal Target As Range)

' pour les changements de dates des blocs A17:A21 jusqu'à A87:A91 ->
changement des cellules connexes
If Not Intersect(Target, Range("A17:A21")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A31:A35")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A45:A49")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A59:A63")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A73:A77")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A87:A91")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changement du nom du client en AJ1 -> changement de toutes
les cellules connexes
If Not Intersect(Target, Range("AJ1")) Is Nothing And Target.Count = 1
And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changements de pré/postfixes des blocs AJ13:AJ14 jusqu'à
AJ83:AJ84 -> changement de toutes les cellules connexes
If Not Intersect(Target, Range("AJ13:AJ14")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If


If Not Intersect(Target, Range("AJ27:AJ28")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ41:AJ42")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ55:AJ56")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ69:AJ70")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ83:AJ84")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91] = "" Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m +
n, 1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83]
& " du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83]
& " du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If

' pour le classement alphabétique des préfixes de la colonne AK
If Not Intersect(Target, Range("AK2:AK1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AK2", [AK1000].End(xlUp)).Sort _
key1:=Range("AK2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabétique des postfixes de la colonne AL
If Not Intersect(Target, Range("AL2:AL1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AL2", [AL1000].End(xlUp)).Sort _
key1:=Range("AL2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du nombre de participants de la colonne
AM
If Not Intersect(Target, Range("AM2:AM1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AM2", [AM1000].End(xlUp)).Sort _
key1:=Range("AM2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du prix/kilomètre de la colonne AN
If Not Intersect(Target, Range("AN2:AN1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AN2", [AN1000].End(xlUp)).Sort _
key1:=Range("AN2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabètique des noms et adresses des colonnes
AO -> AS
If Not Intersect(Target, Range("AO2:AO1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AO2", [AO1000].End(xlUp).Offset(0, 4)).Sort _
key1:=Range("AO2"), order1:=xlAscending
temoin = False
End If




' pour afficher les jours décomptés sur la feuille Fact
If Not Intersect(Target, Range("A17:A91")) Is Nothing And Target.Count
= 1 And Not temoin Then
temoin = True
debut = Application.WorksheetFunction.Max(Range("A17:A91"))
fin = 0
For m = 0 To 83 Step 14
For n = 0 To 4
With Cells(m + n + 17, 1)
If .Value <> "" And Weekday(.Value) > 1 And
Weekday(.Value) < 7 Then
If .Value < debut Then
debut = .Value
End If
If .Value > fin Then
fin = .Value
End If
End If
End With
Next n
Next m
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(16, 1).Value = " Veuillez
trouver ci-joint les honoraires ainsi que les frais de déplacements pour
la période du " & Format(debut, "d") & " au " & Format(fin, "d mmmm
yyyy")
ElseIf debut = fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(16, 1).Value = " Veuillez
trouver ci-joint les honoraires ainsi que les frais de déplacements pour
la période du " & Format(fin, "d mmmm yyyy")
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(16, 1).Value = " Veuillez
trouver ci-joint les honoraires ainsi que les frais de déplacements pour
la période du "
End If
temoin = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

ActiveSheet.Calculate
If Not Intersect([AJ13:AJ86], Target) Is Nothing And Target.Count = 1
Then
For n = 0 To 2
If Target.Row = 13 + n Or Target.Row = 27 + n Or Target.Row =
41 + n Or Target.Row = 55 + n Or Target.Row = 69 + n Or Target.Row = 83 +
n Then
SendKeys "%{DOWN}"
End If
Next n
End If
If Not Intersect([AJ1:AJ2], Target) Is Nothing And Target.Count = 1
Then
SendKeys "%{DOWN}"
End If

h = ActiveCell.Height
w2 = ActiveCell.Width
t = ActiveCell.Top
w = ActiveCell.Left

ActiveSheet.Unprotect
'Teste si les rectangles existent déjà.
On Error Resume Next
ActiveSheet.Shapes("RectangleV").Delete
On Error Resume Next
ActiveSheet.Shapes("RectangleH").Delete
On Error GoTo 0

'Ajoute les rectangles
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, t, w, h).Name =
"RectangleV"
With ActiveSheet.Shapes("RectangleV")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Shapes.AddShape(msoShapeRectangle, w, 0, w2, t).Name =
"RectangleH"

With ActiveSheet.Shapes("RectangleH")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Protect , True, True, True

End Sub









Avatar
François
Bonjour MichDenis,

Je n'ai pas trouvé le moyen dans le VBE de savoir le poids des formules
contenues dans le VBA propre à cette feuille.
En revanche, en supprimant le code de cette seule feuille, le fichier passe
de 277 à 221 Ko, soit 56 Ko - pas loin du seul de 64 que tu as évoqué pour
les modules .... D'autant que je suis sous Excel 2000 ...

Merci pour ton aide
Je vais essayer de condenser au maximum le code et je vais supprimer tous
les commentaires de repérage.

François

"MichDenis" a écrit dans le message de news:

Voici un bout de texte qui commente le poids que peut avoir
le code d'un module... malheureusement, je n'ai plu l'auteur
de ces lignes ...probablement, cette limite ne s'adresse pas
à la version Excel 2007

********************
"VBA has an undocumented "soft limit" on the maximum size of any single
standard code module. A standard code moldule should not exceed 64 KB as
measured by its text file size when exported from the project.Your
project will not crash immediately upon a single module exceeding this
64KB limit, but consistently exceeding this limit will almost invariably
lead to an unstable application."
********************

Et 64 Kb, c'est déjà beaucoup !





"François" a écrit dans le message de news:


Bonjour à tous,

dans une feuille, j'ai une bonne douzaine de macros évenementielles sous
Worksheet_Change et deux sous Worksheet_SelectionChange.

En en rajoutant dans Worksheet_Change, j'ai eu un bug avec comme message :
"Erreur de compilation" " Procédure trop grande" !

Y-a-t-il une limitation au nombre de ces macros ?
Y-a-t-il un moyen de contourner cela ?

Je mets ci-dessous les macros, quoique je ne soit pas sûr que cela soit
utile.
(si nécessaire, je pourrais mettre le fichier en Cjoint)

Merci à tous pour vos suggestions

François

Dim temoin As Boolean
Dim debut As Date, fin As Date
Dim m As Byte, n As Byte
Private Sub Worksheet_Change(ByVal Target As Range)

' pour les changements de dates des blocs A17:A21 jusqu'à A87:A91 ->
changement des cellules connexes
If Not Intersect(Target, Range("A17:A21")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A31:A35")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A45:A49")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A59:A63")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A73:A77")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("A87:A91")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changement du nom du client en AJ1 -> changement de toutes
les cellules connexes
If Not Intersect(Target, Range("AJ1")) Is Nothing And Target.Count = 1
And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If


' pour les changements de pré/postfixes des blocs AJ13:AJ14 jusqu'à
AJ83:AJ84 -> changement de toutes les cellules connexes
If Not Intersect(Target, Range("AJ13:AJ14")) Is Nothing And
Target.Count
= 1 And Not temoin Then
temoin = True
If [A17] = "" And [A18] = "" And [A19] = "" And [A20] = "" And
[A21]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = ""
ActiveSheet.Cells(13, 6).Value = " "
Else
m = 0
debut = Application.WorksheetFunction.Max(Range("A17:A21"))
fin = Application.WorksheetFunction.Min(Range("A17:A21"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(24, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ14]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(24, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(13, 36).Value <> "" Then
ActiveSheet.Cells(13, 6).Value = [AJ1] & " " & [AJ13] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ14]
Else
ActiveSheet.Cells(13, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ14]
End If
End If
End If
temoin = False
End If


If Not Intersect(Target, Range("AJ27:AJ28")) Is Nothing And
Target.Count
= 1 And Not temoin Then
temoin = True
If [A31] = "" And [A32] = "" And [A33] = "" And [A34] = "" And
[A35]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = ""
ActiveSheet.Cells(27, 6).Value = " "
Else
m = 14
debut = Application.WorksheetFunction.Max(Range("A31:A35"))
fin = Application.WorksheetFunction.Min(Range("A31:A35"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(25, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ28]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(25, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(27, 36).Value <> "" Then
ActiveSheet.Cells(27, 6).Value = [AJ1] & " " & [AJ27] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ28]
Else
ActiveSheet.Cells(27, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ28]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ41:AJ42")) Is Nothing And
Target.Count
= 1 And Not temoin Then
temoin = True
If [A45] = "" And [A46] = "" And [A47] = "" And [A48] = "" And
[A49]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = ""
ActiveSheet.Cells(41, 6).Value = " "
Else
m = 28
debut = Application.WorksheetFunction.Max(Range("A45:A49"))
fin = Application.WorksheetFunction.Min(Range("A45:A49"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(26, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ42]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(26, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(41, 36).Value <> "" Then
ActiveSheet.Cells(41, 6).Value = [AJ1] & " " & [AJ41] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ42]
Else
ActiveSheet.Cells(41, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ42]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ55:AJ56")) Is Nothing And
Target.Count
= 1 And Not temoin Then
temoin = True
If [A59] = "" And [A60] = "" And [A61] = "" And [A62] = "" And
[A63]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = ""
ActiveSheet.Cells(55, 6).Value = " "
Else
m = 42
debut = Application.WorksheetFunction.Max(Range("A59:A63"))
fin = Application.WorksheetFunction.Min(Range("A59:A63"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(27, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AD56]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(27, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(55, 36).Value <> "" Then
ActiveSheet.Cells(55, 6).Value = [AJ1] & " " & [AJ55] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ56]
Else
ActiveSheet.Cells(55, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ56]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ69:AJ70")) Is Nothing And
Target.Count
= 1 And Not temoin Then
temoin = True
If [A73] = "" And [A74] = "" And [A75] = "" And [A76] = "" And
[A77]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = ""
ActiveSheet.Cells(69, 6).Value = " "
Else
m = 56
debut = Application.WorksheetFunction.Max(Range("A73:A77"))
fin = Application.WorksheetFunction.Min(Range("A73:A77"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
compteur = compteur + 1
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(28, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ70]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(28, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(69, 36).Value <> "" Then
ActiveSheet.Cells(69, 6).Value = [AJ1] & " " & [AJ69] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ70]
Else
ActiveSheet.Cells(69, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ70]
End If
End If
End If
temoin = False
End If

If Not Intersect(Target, Range("AJ83:AJ84")) Is Nothing And
Target.Count
= 1 And Not temoin Then
temoin = True
If [A87] = "" And [A88] = "" And [A89] = "" And [A90] = "" And
[A91]
= "" Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 1).Value = ""
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = ""
ActiveSheet.Cells(83, 6).Value = " "
Else
m = 70
debut = Application.WorksheetFunction.Max(Range("A87:A91"))
fin = Application.WorksheetFunction.Min(Range("A87:A91"))
For n = 0 To 4
If Cells(17 + m + n, 1) <> "" And Weekday(Cells(17 + m + n,
1)) > 1 And Weekday(Cells(17 + m + n, 1)) < 7 Then
If Cells(17 + m + n, 1).Value < debut Then
debut = Cells(17 + m + n, 1).Value
End If
If Cells(17 + m + n, 1).Value > fin Then
fin = Cells(17 + m + n, 1).Value
End If
End If
Next n
If Weekday(debut) < 2 Or Weekday(debut) > 6 Then
temoin = False
Exit Sub
End If
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(29, 3).Value = fin - debut + 1
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " &
[AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(debut, "d") & " au " & Format(fin, "d mmmm yy") & " " & [AJ84]
End If
Else
Sheets("Fact" & Right(ActiveSheet.Name,
Len(ActiveSheet.Name) - 5)).Cells(29, 1).Value = "- du " & Format(fin, "d
mmmm yy")
If ActiveSheet.Cells(83, 36).Value <> "" Then
ActiveSheet.Cells(83, 6).Value = [AJ1] & " " & [AJ83] &
" du " & Format(fin, "d mmmm yy") & " " & [AJ84]
Else
ActiveSheet.Cells(83, 6).Value = [AJ1] & " du " &
Format(fin, "d mmmm yy") & " " & [AJ84]
End If
End If
End If
temoin = False
End If

' pour le classement alphabétique des préfixes de la colonne AK
If Not Intersect(Target, Range("AK2:AK1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AK2", [AK1000].End(xlUp)).Sort _
key1:=Range("AK2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabétique des postfixes de la colonne AL
If Not Intersect(Target, Range("AL2:AL1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AL2", [AL1000].End(xlUp)).Sort _
key1:=Range("AL2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du nombre de participants de la colonne
AM
If Not Intersect(Target, Range("AM2:AM1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AM2", [AM1000].End(xlUp)).Sort _
key1:=Range("AM2"), order1:=xlAscending
temoin = False
End If

' pour le classement croissant du prix/kilomètre de la colonne AN
If Not Intersect(Target, Range("AN2:AN1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AN2", [AN1000].End(xlUp)).Sort _
key1:=Range("AN2"), order1:=xlAscending
temoin = False
End If

' pour le classement alphabètique des noms et adresses des colonnes
AO -> AS
If Not Intersect(Target, Range("AO2:AO1000")) Is Nothing And
Target.Count = 1 And Not temoin Then
temoin = True
Range("AO2", [AO1000].End(xlUp).Offset(0, 4)).Sort _
key1:=Range("AO2"), order1:=xlAscending
temoin = False
End If




' pour afficher les jours décomptés sur la feuille Fact
If Not Intersect(Target, Range("A17:A91")) Is Nothing And Target.Count
1 And Not temoin Then
temoin = True

debut = Application.WorksheetFunction.Max(Range("A17:A91"))
fin = 0
For m = 0 To 83 Step 14
For n = 0 To 4
With Cells(m + n + 17, 1)
If .Value <> "" And Weekday(.Value) > 1 And
Weekday(.Value) < 7 Then
If .Value < debut Then
debut = .Value
End If
If .Value > fin Then
fin = .Value
End If
End If
End With
Next n
Next m
If debut <> fin Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du " & Format(debut,
"d") & " au " & Format(fin, "d mmmm yyyy")
ElseIf debut = fin Then
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du " & Format(fin, "d
mmmm yyyy")
Else
Sheets("Fact" & Right(ActiveSheet.Name, Len(ActiveSheet.Name) -
5)).Cells(16, 1).Value = " Veuillez trouver ci-joint les honoraires
ainsi que les frais de déplacements pour la période du "
End If
temoin = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

ActiveSheet.Calculate
If Not Intersect([AJ13:AJ86], Target) Is Nothing And Target.Count = 1
Then
For n = 0 To 2
If Target.Row = 13 + n Or Target.Row = 27 + n Or Target.Row =
41
+ n Or Target.Row = 55 + n Or Target.Row = 69 + n Or Target.Row = 83 + n
Then
SendKeys "%{DOWN}"
End If
Next n
End If
If Not Intersect([AJ1:AJ2], Target) Is Nothing And Target.Count = 1
Then
SendKeys "%{DOWN}"
End If

h = ActiveCell.Height
w2 = ActiveCell.Width
t = ActiveCell.Top
w = ActiveCell.Left

ActiveSheet.Unprotect
'Teste si les rectangles existent déjà.
On Error Resume Next
ActiveSheet.Shapes("RectangleV").Delete
On Error Resume Next
ActiveSheet.Shapes("RectangleH").Delete
On Error GoTo 0

'Ajoute les rectangles
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 0, t, w, h).Name > "RectangleV"
With ActiveSheet.Shapes("RectangleV")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Shapes.AddShape(msoShapeRectangle, w, 0, w2, t).Name > "RectangleH"

With ActiveSheet.Shapes("RectangleH")
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
.Line.Weight = 3#
.Line.ForeColor.SchemeColor = 10
.ControlFormat.PrintObject = False
End With

ActiveSheet.Protect , True, True, True

End Sub







Avatar
Misange
Bonjour MichDenis,

Je n'ai pas trouvé le moyen dans le VBE de savoir le poids des formules
contenues dans le VBA propre à cette feuille.
En revanche, en supprimant le code de cette seule feuille, le fichier passe
de 277 à 221 Ko, soit 56 Ko - pas loin du seul de 64 que tu as évoqué pour
les modules .... D'autant que je suis sous Excel 2000 ...


La limite de 64K existe toujours à priori sous 2007, VBA n'ayant pas
subi de modif.
C'est 64K par module. Donc une bonne solution peut être de fractionner
tes macros sur plusieurs modules:
sub ma macro1()
call maprocédure1
maprocédure2...
end sub

mamacro1 sur un module, les maprocedure sur un autre


--
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net

Avatar
François
Bonsoir Misange,

merci beaucoup pour ce coup de pouce plus que judicieux ! ...

François

"Misange" a écrit dans le message de news:

Bonjour MichDenis,

Je n'ai pas trouvé le moyen dans le VBE de savoir le poids des formules
contenues dans le VBA propre à cette feuille.
En revanche, en supprimant le code de cette seule feuille, le fichier
passe de 277 à 221 Ko, soit 56 Ko - pas loin du seul de 64 que tu as
évoqué pour les modules .... D'autant que je suis sous Excel 2000 ...


La limite de 64K existe toujours à priori sous 2007, VBA n'ayant pas subi
de modif.
C'est 64K par module. Donc une bonne solution peut être de fractionner tes
macros sur plusieurs modules:
sub ma macro1()
call maprocédure1
maprocédure2...
end sub

mamacro1 sur un module, les maprocedure sur un autre


--
Misange migrateuse
XlWiki : Participez à un travail collaboratif sur excel !
http://xlwiki.free.fr/wiki
http://www.excelabo.net