Re,
En pièce jointe ton fichier modifié. Il y a trois étapes :
• Une première macro à l'ouverture du classeur :
Private Sub Workbook_Open() 'à l'ouverture du classeur
Module1.ValiDon 'lance la procédure [ValiDon] du module [Module1]
End Sub
Va générer une liste de validation de données, contenant les agences, dans la cellule B1 de l'onglet CeQueJeVoudrais via la macro ValiDon ci-dessous où il te faudra adapter les noms des onglet 1 et 2 :
Public O1 As Worksheet 'déclare la variable O1 (Onglet1)
Public O2 As Worksheet 'déclare la variable O2 (Onglet2)
Public TC As Variant 'déclare la variable TC (Tableau de Cellules)
Public NL As Integer 'déclare la variable NL (Nombre de Lignes)
Public NC As Byte 'déclare la variable NC (Nombre de colonnes)
Public Sub ValiDon() 'Validation de données automatique en B1
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim LISTE As String 'déclare la variable LISTE
Set O1 = Sheets("BaseDeDonnees") 'définit l'onglet O1 (à adapter)
Set O2 = Sheets("CeQueJeVoudrais") 'définit l'onglet O2 (à adapter)
TC = O1.Range("A1").CurrentRegion 'définit le tableau de cellules TC
NL = UBound(TC, 1) 'définit le nombre de lignes NL du tableau de cellules TC
NC = UBound(TC, 2) 'définit le nombre de colonnes NC du tableau de cellules TC
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To NL 'boucle sur toutes les lignes I du tyableau de cellules TC (en partant de la seconde)
D(TC(I, 1)) = "" 'alimente le dictionnaire D avec la valeur ligne I colonne 1 de TC
Next I 'prochaine ligne de la boucle
LISTE = Join(D.keys, ",") 'définit la variable LISTE
With O2.Range("B1").Validation 'prend en compte la validation de données de la cellule B1 de l'onglet O2
.Delete 'supprime une éventuelle ancienne validation de données
.Add xlValidateList, Formula1:=LISTE 'ajoute LISTE comme nouvelle validation de données
End With 'fin de la prise en compte de la validation de données de la cellule B1 de l'onglet O2
End Sub
Veille à garder les variables publiques en haut car elles sont utilisées dans d'autres modules...
• La procédure événementielle Change de l'onglet BaseDeDonnees permet de mettre à jour la liste de validation de données en B1 si une ligne est ajoutée/supprimée ou effacée.
Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
'si le changement a lieu dans la colonne 1, met à jour la validation de données en B1 de l'onglet O2
If Target.Column = 1 Then Module1.ValiDon 'lance la procédure [ValiDon] du module [Module1]
End Sub
• La procédure événementielle Change de l'onglet CeQueJeVoudrais fair le reste et met à jour le tableau en fonction de l'agence choisie en B1 avec le code ci-dessous :
Private Sub Worksheet_Change(ByVal Target As Range) 'au changement dans l'onglet
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim T1 As Variant 'déclare la variable T1 (Tableau1)
Dim T2 As Variant 'déclare la variable T2 (Tableau2)
Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
If Target.Address <> "$B$1" Then Exit Sub 'si le changement a lieu ailleurs qu'en B1, sort de la procédure
Range("B3").CurrentRegion.Offset(1, 0).ClearContents 'efface d'éventuelles anciennes données
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
For I = 2 To NL 'boucle sur toutes les lignes I du tyableau de cellules TC (en partant de la seconde)
'si la valeur ligne I colonne 1 de TC est égale à la valeur de B1,
'alimente le dictionnaire D avec la valeur ligne I colonne 2 de TC
If CStr(TC(I, 1)) = CStr(Target.Value) Then D(TC(I, 2)) = D(TC(I, 2)) + 1
Next I 'prochaine ligne de la boucle
T1 = D.keys 'récupère dans T1 la liste des éléments de TC sans doublons
T2 = D.items 'récupère dans T2 le nombre d'occurrence de chaque élément de T1
'renvoie dans B4 redimensionnée le tableau T1 transposé
O2.Range("B4").Resize(UBound(T1) + 1, 1).Value = Application.Transpose(T1)
'renvoie dans C4 redimensionnée le tableau T2 transposé
O2.Range("C4").Resize(UBound(T2) + 1, 1).Value = Application.Transpose(T2)
Application.ScreenUpdating = False 'affiche les rafraîchissements d'écran
End Sub
Pour que ça fonctionne dans ton fichier original il te faudra recopier toutes ces procédures et les placer dans le même endroit...