• Thème : fonctionnalités d'Excel (validation, tri, filtre, protection, TCD) | Durée : 40 min | Niveau : intermédiaire.
• Classeur de départ : VBA15_achats.xlsm (feuilles Demandes et Analyse).
• Contexte : le service achats centralise les demandes dans un classeur partagé. Vous automatisez les fonctionnalités d'Excel : liste de validation pour le statut, tri, filtre, protection de la feuille et du classeur, puis un tableau croisé dynamique de synthèse par service.

• Ouvrir le classeur, puis l'éditeur Visual Basic (Alt+F11) et le module Module1.
• Créer la liste déroulante du statut : macro CreerListeStatut.
• Trier les demandes par montant décroissant : macro TrierParMontant.
• Filtrer les demandes de la production : macro FiltrerProduction.
• Protéger la feuille et le classeur : macro ProtegerSaisie.
• Créer le tableau croisé dynamique : macro CreerTCD.
• Filtrer le TCD : macro FiltrerTCD.
• Répondre au questionnaire de fin de lab (5 questions, 80 % pour valider).

Sub CreerListeStatut()
    With Worksheets("Demandes").Range("F2:F50").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="En attente;Validée;Refusée"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Statut"
        .InputMessage = "Choisissez un statut dans la liste."
        .ErrorTitle = "Statut inconnu"
        .ErrorMessage = "Utilisez la liste : En attente, Validée ou Refusée."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

• Validation.Add échoue si une validation existe déjà sur la plage : .Delete d'abord rend la macro relançable. Formula1 contient les valeurs de la liste séparées par un point-virgule.
• Résultat attendu :
  • F2:F50 porte une validation de type Liste : En attente, Validée, Refusée
  • Un message de saisie (titre Statut) et un message d'erreur sont définis

Sub TrierParMontant()
    With Worksheets("Demandes").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E2:E13"), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A1:F13")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

• Le tri d'une plage passe par l'objet Sort de la feuille : SortFields définit les clés, SetRange la plage, Header = xlYes protège la ligne d'en-têtes, Apply lance le tri.
• Résultat attendu :
  • La première demande est la plus élevée (DA-105, 7 350 €)
  • Les montants sont rangés du plus grand au plus petit, en-têtes en ligne 1

Sub FiltrerProduction()
    Worksheets("Demandes").Range("$A$1:$F$13").AutoFilter Field:=2, Criteria1:="Production"
End Sub

• AutoFilter pose le filtre automatique sur la plage. Field:=2 désigne la deuxième colonne de la plage (Service), Criteria1 la valeur à garder.
• Résultat attendu :
  • Seules les 5 demandes du service Production restent visibles

Sub ProtegerSaisie()
    With Worksheets("Demandes")
        .Range("F2:F50").Locked = False
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
            AllowFiltering:=True
        .EnableSelection = xlUnlockedCells
    End With
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

• Toutes les cellules sont verrouillées par défaut, mais le verrou n'agit qu'une fois la feuille protégée. On déverrouille la colonne Statut avant Protect. EnableSelection = xlUnlockedCells empêche même de sélectionner les autres cellules.
• Résultat attendu :
  • La feuille Demandes est protégée, la colonne Statut (F2:F50) reste modifiable
  • Seules les cellules déverrouillées sont sélectionnables, le filtre reste utilisable
  • La structure du classeur est protégée

Sub CreerTCD()
    Dim objPivotCache As PivotCache
    Dim objPivotTable As PivotTable
    Set objPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Demandes!A1:F13", Version:=xlPivotTableVersion14)
    Set objPivotTable = objPivotCache.CreatePivotTable(TableDestination:=Range("Analyse!A3"), _
        TableName:="TCD_Achats", DefaultVersion:=xlPivotTableVersion14)
    With objPivotTable.PivotFields("Service")
        .Orientation = xlRowField
        .Position = 1
    End With
    With objPivotTable.PivotFields("Catégorie")
        .Orientation = xlPageField
        .Position = 1
    End With
    objPivotTable.AddDataField objPivotTable.PivotFields("Montant"), Caption:="Total Montant", Function:=xlSum
End Sub

• Un TCD se crée en deux temps : PivotCaches.Create mémorise les données sources, CreatePivotTable place le TCD. Chaque champ prend ensuite une orientation : ligne (xlRowField), filtre (xlPageField) ou valeur (AddDataField).
• La structure du classeur est protégée : le TCD se place donc sur la feuille Analyse existante (Analyse!A3), et non sur une nouvelle feuille.
• Résultat attendu :
  • Le TCD TCD_Achats est placé en Analyse!A3, avec le filtre Catégorie en A1
  • Service en lignes, Catégorie en filtre, somme de Montant en valeurs

Sub FiltrerTCD()
    Dim objPivotTable As PivotTable
    Set objPivotTable = Worksheets("Analyse").PivotTables("TCD_Achats")
    objPivotTable.PivotFields("Catégorie").ClearAllFilters
    objPivotTable.PivotFields("Catégorie").CurrentPage = "Informatique"
    With objPivotTable.PivotFields("Service")
        .PivotItems("Direction").Visible = False
    End With
End Sub

• CurrentPage choisit la valeur d'un champ de filtre. PivotItems("Direction").Visible = False masque un élément d'un champ de ligne. ClearAllFilters remet le champ à zéro : la macro donne le même résultat à chaque exécution.
• Résultat attendu :
  • Le filtre Catégorie affiche Informatique
  • Le service Direction est masqué dans les lignes du TCD (Maintenance 2 120, Production 1 450, Qualité 1 890, total 5 460)

• Méthode Validation.Add : Source Microsoft Learn
• Méthode Worksheet.Protect : Source Microsoft Learn
• Méthode PivotCaches.Create : Source Microsoft Learn