Exercice 5 - Réaliser une macro

Sub ListesDéroulantesSurOnglets()
Dim ws As Worksheet
Dim cell As Range
Dim dropdownRange As String
Dim sheetNames As String
Dim sheet As Worksheet

' Définir la plage de cellules pour la liste déroulante
dropdownRange = "A2" ' Changez cette valeur pour la cellule souhaitée

' Lire les noms des onglets
sheetNames = ""
For Each sheet In ThisWorkbook.Worksheets
sheetNames = sheetNames & sheet.Name & ","
Next sheet

' Retirer la dernière virgule
sheetNames = Left(sheetNames, Len(sheetNames) - 1)

' Ajouter la liste déroulante à chaque onglet
For Each ws In ThisWorkbook.Worksheets
Set cell = ws.Range(dropdownRange)
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sheetNames
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

' Formater la cellule a modifier si vous le souhaitez
cell.RowHeight = 25
cell.ColumnWidth = 12
cell.Font.Bold = True
cell.Font.Color = RGB(0, 0, 0)
cell.HorizontalAlignment = xlCenter
cell.VerticalAlignment = xlCenter
cell.WrapText = True
cell.Interior.Color = RGB(255, 192, 0) ' Couleur de remplissage #7E350E
Next ws
End Sub