BF_LCEB_Decor

Cet article détaille la mécanique de construction du Décor de "BF_LCEB", version MS Excel.

*) Coller les Macros

La "petite nouveauté" de ce générateur de décor, est de se composer de deux procédures et non une seule. Pas d'affolement ;)

- Démarrez MS Excel (versions 2003, 2007, 2010, peu importe..), un classeur par défaut s'ouvre. Si ce n'est pas le cas, ouvrez un "Nouveau Classeur".

- Tapez au clavier la combinaison Alt-F11 (maintenez Alt et pressez la touche "F11"). Vous devez avoir ça à l'écran :


- Cherchez dans l'Explorateur de Projets (Ctrl-R pour l'afficher, si nécessaire) l'item "ThisWorkBook" (ou créez un Module, ça fonctionne aussi), et activez-le en Double-Cliquant dessus.

- Collez dans la Zone de Code (la grande zone blanche à droite) le Code VBA ci-dessous :

Rem: si votre navigateur ne récupère pas correctement les sauts de lignes, collez d'abord ce code dans WordPad, puis re- copiez-le (Ctrl-A et Ctrl-C), ceci "normalisera" les retours chariot..

Sub Decor_Part_1()
' Suppression
Application.DisplayAlerts = False
While Sheets.Count > 1: Sheets(Sheets.Count).Delete: Wend
' Mise en Forme
With Sheets(1)
    ' Nommage
    .Name = "BruteForce"
    ' Global
    With .Cells
        .RowHeight = 15.75: .ColumnWidth = 2.14
        .HorizontalAlignment = xlCenter: .VerticalAlignment = xlCenter
        With .Font
            .Name = "Calibri": .Size = 11
        End With
    End With
    .Range("B:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG").ColumnWidth = 5
    ' Cacher Bas
    With .Range("7" + Mid$(.Cells.Address, 3))
        .EntireColumn.Hidden = True: .EntireRow.Hidden = True
    End With
    ' Réafficher Zone
    With .Range("A1:AH6")
        .EntireRow.Hidden = False: .EntireColumn.Hidden = False
    End With
    ' Figer Volet
    With ActiveWindow
        .SplitRow = 4: .FreezePanes = True
    End With
    .Range("A5").Select
    ' Textes
    .Range("B4").FormulaR1C1 = "Res": .Range("C4").FormulaR1C1 = "Delta"
    .Range("B5").FormulaR1C1 = "nnn": .Range("C5").FormulaR1C1 = "nnn"
    .Range("E4").FormulaR1C1 = "Opération 1": .Range("E5").FormulaR1C1 = "nnn"
    .Range("F5").FormulaR1C1 = "X": .Range("G5").FormulaR1C1 = "nnn"
    .Range("H5").FormulaR1C1 = "=": .Range("I5").FormulaR1C1 = "nnn"
    .Range("C2").FormulaR1C1 = "Tirage:": .Range("F2").FormulaR1C1 = ";"
    .Range("H2").FormulaR1C1 = ";": .Range("J2").FormulaR1C1 = ";"
    .Range("L2").FormulaR1C1 = ";": .Range("N2").FormulaR1C1 = ";"
    .Range("R2").FormulaR1C1 = "A trouver:": .Range("V2").FormulaR1C1 = "Delta Maxi:"
    .Range("AA2").FormulaR1C1 = "Le Compte Est Bon (P)MyLzz59"
    ' Fusions
    With .Range("C2:D2")
        .Merge: .HorizontalAlignment = xlRight
    End With
    With .Range("R2:T2")
        .Merge: .HorizontalAlignment = xlRight
    End With
    With .Range("V2:X2")
        .Merge: .HorizontalAlignment = xlRight
    End With
    .Range("AA2:AG2").Merge
    ' Bordures
    With .Range("C2:O2")
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    With .Range("R2:U2")
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    
    With .Range("V2:Y2")
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    With .Range("AA2:AG2")
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
    End With
    With Range("B4:C5")
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
    End With
    ' Bloc Opération 1
    .Range("E4:I4").Merge
    With .Range("E4:I5")
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous: .Weight = xlMedium: .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
        End With
    End With
    ' Duplication
    .Range("E4:I5").Copy
    .Paste .Range("K4"): .Paste .Range("Q4"): .Paste .Range("W4"): .Paste .Range("AC4")
    Application.CutCopyMode = False
    .Range("K4:O4").FormulaR1C1 = "Opération 2": .Range("Q4:U4").FormulaR1C1 = "Opération 3"
    .Range("W4:AA4").FormulaR1C1 = "Opération 4": .Range("AC4:AG4").FormulaR1C1 = "Opération 5"
    ' Italique
    .Range("C2:D2,R2:T2,V2:X2,AA2:AG2,B4,C4,E4:I4,K4:O4,Q4:U4,W4:AA4,AC4:AG4").Font.Italic = True
    ' Bouton "Tirer"
    .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, _
        Left:=3.75, Top:=12, Width:=42.75, Height:=23.25).Name = "C_Tirage"
    ' Bouton "Chercher"
    .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, _
        Left:=348.75, Top:=12, Width:=42.75, Height:=23.25).Name = "C_Calc"
    ' Protection Feuille
    .Range("E2,G2,I2,K2,M2,O2,U2,Y2").Locked = False
    .Protect ""
End With
MsgBox "'Decor_Part_1' exécuté." + vbCrLf + "Lancez maintenant 'Decor_Part_2'", vbInformation
End Sub

Sub Decor_Part_2()
' Bouton "Tirer"
With Sheets(1).C_Tirage
    .BackColor = &HFFFFC0: .TakeFocusOnClick = False
    .Font.Name = "Arial": .Font.Size = 8: .Caption = "Trier"
End With
' Bouton "Chercher"
With Sheets(1).C_Calc
    .BackColor = &HC0FFC0: .TakeFocusOnClick = False
    .Font.Name = "Arial": .Font.Size = 8: .Caption = "Chercher"
End With
MsgBox "'Decor_Part_2' exécuté." + vbCrLf + "Vous pouvez supprimer le module.", vbInformation
End Sub

*) Exécuter les Macros

- Revenez à la fenêtre d'Excel (vous pouvez refermer ou icôniser l'Editeur VBA)

- Appelez la fenêtre de Lancement des Macros en pressant ALT-F8
Ceci s'affiche :


- Double-Cliquez sur "Decor_Part_1" (ou "ThisWorkBook.Decor_Part_1") Vous obtenez ceci :


- Validez le Pop-Up, et rappelez la fenêtre de Lancement des Macros (Alt-F8)

- Double-Cliquez sur "Decor_Part_2"
(ou "ThisWorkBook.Decor_Part_2"). Et voilà :


*) Supprimer les Macros

- A ce stade, le Code VBA du Décor ne vous sert plus à rien, retournez dans l'Editeur VBA (Alt-F11) et effacez simplement l'intégralité du code.

*) Enregistrer le Classeur

- Avant de passer à la suite, il est judicieux de sauvegarder le Classeur ainsi "customisé".

ATTENTION !! A partir de la version 2007, Excel propose par défaut un format d'enregistrement ".xlsX" qui ne convient PAS, puisqu'il détruit le VBA. Choisissez ".xls" (pour rester compatible avec les versions antérieures d'Excel), ou ".xlsM" (qui garde les Macros, autrement dit le Code VBA).

- Pensez à nommer votre Classeur, par exemple "BF_LCEB".


C'est fait ! Vous pouvez revenir à l'article principal (BF_LCEB)..

-MyLzz59-