Lancement d'une macro événementielle

Ancien1957 -

Bonjour,
J'aimerais lancer une macro en [A1] dès que K1<>"", lancer la même macro en [A25] dès que K2<>""; la même macro en [A50] dès que K3<>"" et ainsi de suite jusqu'à K500.
Est-ce réalisable ?
Merci pour votre aide.
Passez une bonne soirée.

Afficher la suite

A voir également

Lancement d'une macro événementielle
Telecharger macro convertir chiffre en lettre excel - Télécharger - Tableur
Macro word - Guide
Macro recorder - Télécharger - Divers Utilitaires
Jitbit macro recorder - Télécharger - Confidentialité
Lancement au demarrage windows - Guide

11 réponses

Résumé de la discussion

L'objectif est d'exécuter une macro en A1 lorsque K1 n'est pas vide, puis en A25 lorsque K2 n'est pas vide, et ainsi de suite jusqu'à A50 et K500, via une logique répétitive. Plusieurs interventions évoquent des solutions liées à la mise en page et à l'impression des bulletins, indiquant ajustements comme bordures et configuration paysage, plutôt que des détails VBA. D'autres apportent des pistes non VBA ou à faible complexité, comme saisir le nom dans K et générer le bulletin, ou envisager une approche sans code et organiser les feuilles. En cas de progression, la discussion souligne la nécessité de modifier le code enregistré et de clarifier l'objectif, tout en restant conscient des limites d'une macro reliant plusieurs emplacements.

Réponse 1 / 11
f894009
Bonjour,
lancer une macro en [A1] dès que K1<>""
Pouvez etre plus precis?? Car K1<>"" oj=k, mais lancer la macro en A1 !!!!!!

0

Réagir
Réponse 2 / 11
Ancien1957
Lancer ma macro en [A1] dès que la cellule "K1" contient du texte ou un nombre. Si la cellule "K1" est vide, supprimer ou effacer ce que la macro a exécuté. Lancer la même macro en [A25] si la cellule "K2" contient du texte ou des chiffres. Si la cellule "K2" est vide, supprimer ou effacer ce que la macro a exécuté.....ainsi de suite jusqu'à ce qu'on arrive à la cellule "K500".
Merci mon ami.

0

Réagir
1. fabien25000
  
  Bonsoir,
  
  Je pense que la question était : Quelle macro? que doit elle faire?
  
  jusqu'à ce qu'on arrive à la cellule "K500"
  si je suis ton raisonnement K500 déclenche une réaction de la macro dans la case A12500?
  
  0
  
  Réagir
2. ancien1957
  
  @fabien25000La macro exécute automatiquement un bulletin de relevé de notes vierge en " A1 " lorsque on saisit du texte ou des chiffres dans "K1".
  
  0
  
  Réagir
3. f894009
  
  @ancien1957Re,
  
  une macro doit s'executer ex:
  
  si en K1 saisie de texte ou chiffres: le resultat de cette macro s'inscrit en A1 et autres je suppose.
  si raz K1: raz cellules A1 et autres (faudrait preciser, a priori A24 pour K1)
  
  Est-ce ceci que vous voulez ??
  
  0
  
  Réagir
Réponse 3 / 11
ancien1957
La macro doit s'executer de "A1" à "H24" exactement lorsque on saisit du texte ou des chiffres dans la cellule "K1". Si "K1" est effacé ou supprimé, le bulletin de relevé de notes réalisé par la macro doit être aussi supprimé.
Même chose si "K2" contient du texte ou des chiffres, la même macro s’exécute de "A25" à "H49" etc... ainsi de suite jusqu'à "K500".
Merci.

0

Réagir
1. f894009
  
  Bonjour,
  VBA de la feuille:
  Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Not Application.Intersect(Target, Range("K1:K500")) Is Nothing Then CelD = Target.Row * 24 - 23 CelF = Target.Row * 24 Call macro(CelD, CelF) End If End Sub
  
  VBA Module
  Sub macro(CelD, CelF) x = CelD y = CelF End Sub
  
  0
  
  Réagir
Réponse 4 / 11
Ancien1957
Il ne se passe rien lorsque je saisis du texte dans "K1". La macro ne s'exécute pas.

0

Réagir
1. f894009
  
  Re,
  Ben, si vous ne mettez pas de point d'arret sur le end sub de macro(), vous ne verrez rien, puisque la procedure ne fait rien je montre un principe de passage de parametre a une procedure. Vu que vous ne donnez pas votre macro, nous risquons de tourner en rond un bon bout de temps
  Vous connaissez le VBA Excel, les points d'arret pour voir le contenu des variables en passant le curseur souris dessus ??
  
  0
  
  Réagir
2. ancien1957
  
  @f894009Sub Macro1()
  '
  ' Macro1 Macro
  '
  
  '
  Range("A1:H24").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlInsideVertical)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlInsideHorizontal)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  Range("A1:B2").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A1:B2").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlTop
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  ActiveCell.FormulaR1C1 = "ALFRED DUJARDIN"
  Range("A1:B2").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("G1:H2").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  ActiveCell.FormulaR1C1 = "Classe: 4e B"
  Range("G1:H2").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("C3:F4").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  ActiveCell.FormulaR1C1 = "Relevé de notes du Trimestre 2"
  Range("C3:F4").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.Font.Bold = True
  With Selection.Font
  .Name = "Calibri"
  .Size = 20
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With Selection.Font
  .Name = "Calibri"
  .Size = 18
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Selection.Font.Italic = True
  Range("A6").Select
  ActiveCell.FormulaR1C1 = "Matières"
  Range("A7").Select
  ActiveCell.FormulaR1C1 = "Français"
  Range("A8").Select
  ActiveCell.FormulaR1C1 = "Anglais"
  Range("A9").Select
  ActiveCell.FormulaR1C1 = "Mayhs"
  Range("A9").Select
  ActiveCell.FormulaR1C1 = "Maths"
  Range("A10").Select
  ActiveCell.FormulaR1C1 = "Physique"
  Range("A11").Select
  ActiveCell.FormulaR1C1 = "Hist-Géo"
  Range("A12").Select
  ActiveCell.FormulaR1C1 = "Sciences Nat"
  Range("A13").Select
  Columns("A:A").ColumnWidth = 12.29
  Columns("A:A").ColumnWidth = 11.57
  Columns("A:A").ColumnWidth = 11.29
  ActiveCell.FormulaR1C1 = "Sport"
  Range("A14").Select
  ActiveCell.FormulaR1C1 = "Dessin"
  Range("A15").Select
  ActiveCell.FormulaR1C1 = "Musique"
  Range("B6").Select
  ActiveCell.FormulaR1C1 = "Devoir1"
  Range("C6").Select
  ActiveCell.FormulaR1C1 = "Devoir2"
  Range("D6").Select
  ActiveCell.FormulaR1C1 = "Compos"
  Range("E6").Select
  ActiveCell.FormulaR1C1 = "Compos"
  Range("F6").Select
  ActiveCell.FormulaR1C1 = "Coeff"
  Range("G6").Select
  Columns("F:F").ColumnWidth = 5.14
  Columns("B:B").ColumnWidth = 7.14
  Columns("C:C").ColumnWidth = 7.14
  ActiveWindow.SelectedSheets.PrintPreview
  Columns("H:H").ColumnWidth = 34.14
  ActiveCell.FormulaR1C1 = "Total"
  Range("H6").Select
  ActiveCell.FormulaR1C1 = "Observations du prof"
  Range("I6").Select
  Columns("B:B").ColumnWidth = 8.86
  Columns("C:C").ColumnWidth = 9
  Range("D6").Select
  ActiveCell.FormulaR1C1 = "Moy devoirs"
  Range("F2").Select
  Columns("D:D").ColumnWidth = 11.29
  Rows("6:6").Select
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("A6").Select
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Rows("6:6").Select
  Selection.Font.Bold = True
  Range("C3:F4").Select
  Columns("H:H").ColumnWidth = 45.43
  Columns("A:A").ColumnWidth = 13
  Range("C3:G4").Select
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.UnMerge
  With Selection
  .HorizontalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("F7").Select
  ActiveCell.FormulaR1C1 = "3"
  Range("F8").Select
  ActiveCell.FormulaR1C1 = "2"
  Range("F9").Select
  ActiveCell.FormulaR1C1 = "4"
  Range("F10").Select
  ActiveCell.FormulaR1C1 = "2"
  Range("F11").Select
  ActiveCell.FormulaR1C1 = "3"
  Range("F12").Select
  ActiveCell.FormulaR1C1 = "2"
  Range("F13").Select
  ActiveCell.FormulaR1C1 = "1"
  Range("F14").Select
  ActiveCell.FormulaR1C1 = "1"
  Range("F15").Select
  ActiveCell.FormulaR1C1 = "1"
  Range("F16").Select
  ActiveCell.FormulaR1C1 = "19"
  Range("E16").Select
  ActiveCell.FormulaR1C1 = "Total"
  Range("H16").Select
  ActiveCell.FormulaR1C1 = "Absences:"
  Range("H16").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("E16").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("A17:B17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  ActiveCell.FormulaR1C1 = "Moyenne du Trimestre"
  Range("A18:B19").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A17:B19").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Range("E16:E17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("F16:F17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.Font.Bold = True
  Range("G16:G17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("E16:G17").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlInsideVertical)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("F7:F15").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("G7:G17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  Range("B7:E15").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("G7:G17").Select
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  Range("H17").Select
  Columns("H:H").ColumnWidth = 58.71
  Columns("G:G").ColumnWidth = 14.57
  Columns("G:G").ColumnWidth = 11
  Columns("H:H").ColumnWidth = 51.14
  Columns("H:H").ColumnWidth = 40.71
  Columns("G:G").ColumnWidth = 9
  Columns("B:B").ColumnWidth = 8.29
  Columns("C:C").ColumnWidth = 8.29
  Columns("D:D").ColumnWidth = 10.14
  Columns("E:E").ColumnWidth = 8.14
  Columns("G:G").ColumnWidth = 7.14
  Columns("A:A").ColumnWidth = 11.43
  Range("C3:G4").Select
  With Selection.Font
  .Name = "Calibri"
  .Size = 16
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Columns("H:H").ColumnWidth = 38.43
  Columns("H:H").ColumnWidth = 35.71
  Columns("H:H").ColumnWidth = 33.86
  Columns("H:H").ColumnWidth = 29.57
  Columns("G:G").ColumnWidth = 5.86
  Columns("G:G").ColumnWidth = 4.57
  Columns("E:E").ColumnWidth = 7.43
  Columns("E:E").ColumnWidth = 6.29
  Range("E6").Select
  ActiveCell.FormulaR1C1 = "Compo"
  Range("C9").Select
  Rows("6:6").RowHeight = 27.75
  Range("D6").Select
  ActiveCell.FormulaR1C1 = "Moy Dev"
  Range("H8").Select
  Columns("D:D").ColumnWidth = 7.43
  Columns("C:C").ColumnWidth = 7
  Columns("B:B").ColumnWidth = 6.14
  Columns("B:B").ColumnWidth = 6.43
  Columns("B:B").ColumnWidth = 6.71
  Columns("G:G").ColumnWidth = 6.14
  Columns("B:B").ColumnWidth = 7
  Columns("H:H").ColumnWidth = 32.14
  Columns("H:H").ColumnWidth = 31.57
  Columns("H:H").ColumnWidth = 31.71
  Columns("H:H").ColumnWidth = 32
  Columns("H:H").ColumnWidth = 32.14
  Columns("H:H").ColumnWidth = 32
  Range("C3:G4").Select
  With Selection.Font
  .Name = "Calibri"
  .Size = 14
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Range("C1:F2").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("C1:H2").Select
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.UnMerge
  With Selection
  .HorizontalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  With Selection
  .HorizontalAlignment = xlRight
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("A3:B5").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("H3:H5").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("C5:G5").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A3:H5").Select
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.UnMerge
  With Selection
  .HorizontalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A1:H2").Select
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.UnMerge
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A1:H2").Select
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("A1:H2").Select
  ActiveCell.FormulaR1C1 = _
  "ALFRED DUJARDIN Classe: 4e B"
  Range("L12").Select
  ActiveWindow.SmallScroll Down:=4
  Range("D7").Select
  ActiveCell.FormulaR1C1 = "=(RC[-2]+RC[-1])/2"
  Range("G7").Select
  ActiveCell.FormulaR1C1 = "=(RC[-3]*2+RC[-2]*3)/5"
  Range("D7").Select
  Selection.AutoFill Destination:=Range("D7:D15"), Type:=xlFillDefault
  Range("D7:D15").Select
  Range("G7").Select
  Selection.AutoFill Destination:=Range("G7:G15"), Type:=xlFillDefault
  Range("G7:G15").Select
  Range("G16:G17").Select
  ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
  Range("A18:B19").Select
  ActiveCell.FormulaR1C1 = "=(R[-2]C[6]/R[-2]C[5])"
  Range("A18:B19").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection.Font
  .Name = "Calibri"
  .Size = 14
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Selection.Font.Bold = True
  Range("F16:F17").Select
  With Selection.Font
  .Name = "Calibri"
  .Size = 14
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Range("G16:G17").Select
  With Selection.Font
  .Name = "Calibri"
  .Size = 14
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Selection.Font.Bold = True
  Range("H18").Select
  ActiveCell.FormulaR1C1 = "Récompense ou sanction"
  Range("H18").Select
  With Selection
  .HorizontalAlignment = xlRight
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("H18").Select
  With Selection
  .HorizontalAlignment = xlRight
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("H19:H20").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  ActiveCell.FormulaR1C1 = _
  "=IF(R[-1]C[-7]<8,""Blâme"",IF(R[-1]C[-7]<10,""Avertissement"",IF(R[-1]C[-7]<12,"""",IF(R[-1]C[-7]<13.5,""Tableau d'honneur"",IF(R[-1]C[-7]<15,""Encouragements"",IF(R[-1]C[-7]<18,""Félicitations"",""Excellent""))))))"
  Range("H18:H20").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  End With
  Range("H19:H20").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("A22:H24").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A20:G21").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("A16:D16").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("C17:D19").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("E18:G19").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("C17:D19").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  Selection.Borders(xlEdgeLeft).LineStyle = xlNone
  Selection.Borders(xlEdgeTop).LineStyle = xlNone
  Selection.Borders(xlEdgeBottom).LineStyle = xlNone
  Selection.Borders(xlEdgeRight).LineStyle = xlNone
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("A17:B19").Select
  Range("A18").Activate
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Range("E16:G17").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlInsideVertical)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("E18:G19").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  Selection.Borders(xlEdgeLeft).LineStyle = xlNone
  Selection.Borders(xlEdgeTop).LineStyle = xlNone
  Selection.Borders(xlEdgeBottom).LineStyle = xlNone
  Selection.Borders(xlEdgeRight).LineStyle = xlNone
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("A20:G21").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  Selection.Borders(xlEdgeLeft).LineStyle = xlNone
  Selection.Borders(xlEdgeTop).LineStyle = xlNone
  Selection.Borders(xlEdgeBottom).LineStyle = xlNone
  Selection.Borders(xlEdgeRight).LineStyle = xlNone
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("H21").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  Selection.Borders(xlEdgeLeft).LineStyle = xlNone
  Selection.Borders(xlEdgeTop).LineStyle = xlNone
  Selection.Borders(xlEdgeBottom).LineStyle = xlNone
  Selection.Borders(xlEdgeRight).LineStyle = xlNone
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("E16:G17").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlInsideVertical)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  Range("H18:H20").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Range("A17:B19").Select
  Range("A18").Activate
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlMedium
  End With
  Selection.Borders(xlInsideVertical).LineStyle = xlNone
  Range("E18:G19").Select
  ActiveCell.FormulaR1C1 = ""
  Range("A20:G21").Select
  ActiveCell.FormulaR1C1 = "Observations du Directeur:"
  Range("A20:G21").Select
  ActiveCell.FormulaR1C1 = "Observations du Directeur:"
  With ActiveCell.Characters(Start:=1, Length:=1).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=2, Length:=24).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleSingle
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=26, Length:=1).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Range("A20:G21").Select
  ActiveCell.FormulaR1C1 = "Observations du Directeur:"
  With ActiveCell.Characters(Start:=1, Length:=1).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=2, Length:=24).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleSingle
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=26, Length:=1).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Range("A20:G21").Select
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlTop
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("A20:G21").Select
  ActiveCell.FormulaR1C1 = "Observations du Directeur:"
  With ActiveCell.Characters(Start:=1, Length:=0).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=1, Length:=25).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleSingle
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=26, Length:=1).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Range("A20:G21").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlTop
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlTop
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Range("A20:G21").Select
  ActiveCell.FormulaR1C1 = "Observations du Directeur:"
  With ActiveCell.Characters(Start:=1, Length:=0).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=1, Length:=25).Font
  .Name = "Calibri"
  .FontStyle = "Gras"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleSingle
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  With ActiveCell.Characters(Start:=26, Length:=1).Font
  .Name = "Calibri"
  .FontStyle = "Normal"
  .Size = 11
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ThemeColor = xlThemeColorLight1
  .TintAndShade = 0
  .ThemeFont = xlThemeFontMinor
  End With
  Range("H16").Select
  ActiveCell.FormulaR1C1 = ""
  Range("H17").Select
  ActiveCell.FormulaR1C1 = "Absences:"
  Range("H17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Range("H16:H17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  With Selection
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  End With
  Selection.UnMerge
  Range("H16:H17").Select
  With Selection
  .HorizontalAlignment = xlCenter
  .VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = False
  End With
  Selection.Merge
  Range("J8").Select
  ActiveWindow.SmallScroll Down:=-4
  Range("A1:H24").Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  Range("A3:H5").Select
  End Sub
  
  0
  
  Réagir
3. f894009
  
  @ancien1957Bonjour,
  
  C'est pas une macro, c'est un roman, mais bon faut voir. Petite question, pourquoi executer cette macro si changement d'une cellule en colonne K de 1 a 500, parce une fois les bulletins crees .... ????
  
  Pendant que nous y sommes, vous ecrivez quoi dans les cellules K, nom prenom ou ????
  
  Votre macro peut etre simplifiee
  
  0
  
  Réagir
4. ancien1957
  
  @f894009Dans la cellule "K", je saisis le nom et le prénom de chaque élève. C'est pour gagner du temps . Il suffit de saisir un nom pour voir un bulletin de notes exécuté.
  Merci pour tout.
  
  0
  
  Réagir
5. f894009
  
  @ancien1957Re,
  
  Ok ca roule, je fais
  
  0
  
  Réagir
Vous n’avez pas trouvé la réponse que vous recherchez ?

La communauté CCM répond en moyenne en moins de 2h.

Posez votre question
Réponse 5 / 11
f894009
Bonjour,

une facon de faire: https://mon-partage.fr/f/vZ1l72gr/

a vous de voir

Les noms et classes sur une feuille et les bulletins sur un autre feuille serait peut-etre a faire...... C'est vous qui voyez

0

Réagir
Réponse 6 / 11
ancien1957
Bonjour à vous.
Je suis d'accord.

0

Réagir
Réponse 7 / 11
f894009
Re,

fichier avec onglet Liste Eleves et onglet Buletins

https://mon-partage.fr/f/V6Xip3i9/

0

Réagir
Réponse 8 / 11
ancien1957
OK mon ami

0

Réagir
Réponse 9 / 11
ancien1957
ça marche à merveille.
Merci pour tous les désagréments que je vous ai causé.
Passez une bonne soirée et encore une fois merci.

0

Réagir
1. f894009
  
  Re,
  
  Merci pour tous les désagréments que je vous ai causé.
  L'essentiel est d'arrive au but recherche
  
  0
  
  Réagir
Réponse 10 / 11
ancien1957
Pouvez-vous m'écrire convenablement la formule excel suivante:
=RECHERCHE(feuilListeEleves!A2;A2:A25;B2:B25) sachant que la feuille dans laquelle s'effectue la recherche s'appelle Liste Eleves alors que la feuille de destination s'appelle Bulletins.
Merci

0

Réagir
Réponse 11 / 11
ancien1957
Je pense qu'il y a un petit problème là.
Lorsque j'efface les noms de la liste d'élèves, le bulletin ne s'efface pas.
Aussi, mon bulletin n'occupe que la moitié d'une feuille de format A4 alors qu'il devrait en occuper la totalité.
Les cellules F16 et G16 doivent être bordées.
Merci mon ami

0

Réagir
1. f894009
  
  Bonjour,
  Je pense qu'il y a un petit problème là.
  Non, y a pas de probleme.Vous n'avez pas demande d'effacer le bulletin si raz du nom ni la mise en page des bulletin pour impression.
  
  En effet manque une bordure F16/G17
  
  que la moitié d'une feuille de format A4
  C'est tout ce qu"il occupe.........!!!!
  
  Fichier avec bordure et effacement: https://mon-partage.fr/f/iiZqKdRR/
  
  Pour la mise en page A4 paysage, je regarde les modifs a faire.
  Question: un bulletin papier standard a quelles dimensions
  
  0
  
  Réagir
2. ancien1957
  
  @f894009Les dimensions d'un bulletin sont équivalentes à celle d'un format A4 tout en laissant une marge de 0,5cm de chaque coté.
  Salut mon ami.
  
  0
  
  Réagir
3. f894009
  
  @ancien1957Re,
  
  A4, paysage: modele:https://mon-partage.fr/f/DXL47D40/
  
  Si plus "grand", vous pourrez le lire a 5m sans lunettes
  
  0
  
  Réagir
4. ancien1957
  
  @f894009Il n y a pas assez de place pour les observations du directeur. Pas de place aussi pour le cachet de l'établissement.
  J'aurais aimé A4 Portrait et qu'il occupe la totalité de la page (de format A4).
  Merci.
  
  0
  
  Réagir
5. f894009
  
  @ancien1957Re,
  
  Pas de place aussi pour le cachet de l'établissement.
  Tampon ou image incorporee ??
  
  J'aurais aimé A4 Portrait
  Je fais, mais regardez deja avec le fichier de creation bulletins, imprimer A4 portrait vous verrez que ca va faire un peu bizzare
  
  0
  
  Réagir

Lancement d'une macro événementielle

11 réponses

Discussions similaires

Newsletters