j'ai finalement optée pour al listbox
maintenant j'arrive à séléctionner plusiers éléments mais je n'arrive pas à les affecter aux filtres de TCD
l'enregistement d'une macro m'a donné ce code que je n'arrive pas à automatiser
Sub multifiltre()
'
' multifiltre Macro
Application.ScreenUpdating = False
Dim prodWorksheet As Worksheet, testWorksheet As Worksheet, ScenarioProd As Worksheet
Dim prodWS As Worksheet, testWS As Worksheet
Dim Max As Worksheet
Dim IHMdate As String
Dim ScenName As String
Dim BookName As String
Dim CcyName As String
Dim ScenIDProd, ScenIDHomolo As String
Dim BookIDProd, BookIDHomolo As String
Dim CcyIDProd, CcyIDHomolo As String
Dim ScenType As String
Dim ScenID As String
Dim ccy As String
Dim TradeTyp As String
Dim Book As String
Dim i As Integer
Dim nbcells As Integer
Set prodWorksheet = ThisWorkbook.Worksheets("PRODZoom")
Set testWorksheet = ThisWorkbook.Worksheets("TESTZoom")
Set prodWS = ThisWorkbook.Worksheets("PRODBookTradeType")
Set testWS = ThisWorkbook.Worksheets("TESTBookTradeType")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''' variables a insérer pour automatiser les changement de filtres via ihm
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
IHMdate = UserForm1.TB_Date.Value
ScenType = UserForm1.ListBox1.ListIndex
CcyName = UserForm1.ComboBox2.Value
ScenName = UserForm1.CB_scenaID.Value
'''
ScenIDProd = Application.WorksheetFunction.VLookup(ScenName, ThisWorkbook.Worksheets("ScenarioProd").Range("A:B"), 2, False)
ScenIDHomolo = Application.WorksheetFunction.VLookup(ScenName, ThisWorkbook.Worksheets("ScenarioHomolo").Range("A:B"), 2, False)
'
CcyIDProd = Application.WorksheetFunction.VLookup(CcyName, ThisWorkbook.Worksheets("RiskCcyProd").Range("A:B"), 2, False)
CcyIDHomolo = Application.WorksheetFunction.VLookup(CcyName, ThisWorkbook.Worksheets("RiskCcyHomolo").Range("A:B"), 2, False)
'
Sheets("ProdDATA").Select
ActiveSheet.PivotTables("PROD").PivotFields( _
"[Scenario].[Scenario Type].[Scenario Type]").VisibleItemsList = Array( _
"[Scenario].[Scenario Type].&[IR]", "[Scenario].[Scenario Type].&[IRVOL]", _
"[Scenario].[Scenario Type].&[IRLONG]", "[Scenario].[Scenario Type].&[IRSHORT]", _
"[Scenario].[Scenario Type].&[SPREADLOCK]", _
"[Scenario].[Scenario Type].&[IRVOLCORREL]", _
"[Scenario].[Scenario Type].&[InflationRate]", _
"[Scenario].[Scenario Type].&[SPREADOISIBOR]", _
"[Scenario].[Scenario Type].&[SPREADPAYSCASH]", _
"[Scenario].[Scenario Type].&[SPREADSWAPCASH]", _
"[Scenario].[Scenario Type].&[SPREADFUTURETITRE]", _
"[Scenario].[Scenario Type].&[DELTABASISINTERCUR]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR1]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR2]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR3]", _
"[Scenario].[Scenario Type].&[INFLATIONRATEVOLATILITY]", _
"[Scenario].[Scenario Type].&[AssetSwapInflationSpread]")
Sheets("TestDATA").Select
ActiveSheet.PivotTables("TEST").CubeFields(21).EnableMultiplePageItems = True
ActiveSheet.PivotTables("TEST").PivotFields( _
"[Scenario].[Scenario Type].[Scenario Type]").VisibleItemsList = Array( _
"[Scenario].[Scenario Type].&[IR]", "[Scenario].[Scenario Type].&[IRVOL]", _
"[Scenario].[Scenario Type].&[IRLONG]", "[Scenario].[Scenario Type].&[IRSHORT]", _
"[Scenario].[Scenario Type].&[SPREADLOCK]", _
"[Scenario].[Scenario Type].&[IRVOLCORREL]", _
"[Scenario].[Scenario Type].&[InflationRate]", _
"[Scenario].[Scenario Type].&[SPREADOISIBOR]", _
"[Scenario].[Scenario Type].&[SPREADPAYSCASH]", _
"[Scenario].[Scenario Type].&[SPREADSWAPCASH]", _
"[Scenario].[Scenario Type].&[SPREADFUTURETITRE]", _
"[Scenario].[Scenario Type].&[DELTABASISINTERCUR]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR1]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR2]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR3]", _
"[Scenario].[Scenario Type].&[INFLATIONRATEVOLATILITY]", _
"[Scenario].[Scenario Type].&[AssetSwapInflationSpread]")
Sheets("PRODBookTradeType").Select
ActiveSheet.PivotTables("PRODBookTT").PivotFields( _
"[Scenario].[Scenario Type].[Scenario Type]").VisibleItemsList = Array( _
"[Scenario].[Scenario Type].&[IR]", "[Scenario].[Scenario Type].&[IRVOL]", _
"[Scenario].[Scenario Type].&[IRLONG]", "[Scenario].[Scenario Type].&[IRSHORT]", _
"[Scenario].[Scenario Type].&[SPREADLOCK]", _
"[Scenario].[Scenario Type].&[IRVOLCORREL]", _
"[Scenario].[Scenario Type].&[InflationRate]", _
"[Scenario].[Scenario Type].&[SPREADOISIBOR]", _
"[Scenario].[Scenario Type].&[SPREADPAYSCASH]", _
"[Scenario].[Scenario Type].&[SPREADSWAPCASH]", _
"[Scenario].[Scenario Type].&[SPREADFUTURETITRE]", _
"[Scenario].[Scenario Type].&[DELTABASISINTERCUR]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR1]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR2]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR3]", _
"[Scenario].[Scenario Type].&[INFLATIONRATEVOLATILITY]", _
"[Scenario].[Scenario Type].&[AssetSwapInflationSpread]")
Sheets("TESTBookTradeType").Select
ActiveSheet.PivotTables("TESTBookTT").CubeFields(21).EnableMultiplePageItems = _
True
ActiveSheet.PivotTables("TESTBookTT").PivotFields( _
"[Scenario].[Scenario Type].[Scenario Type]").VisibleItemsList = Array( _
"[Scenario].[Scenario Type].&[IR]", "[Scenario].[Scenario Type].&[IRVOL]", _
"[Scenario].[Scenario Type].&[IRLONG]", "[Scenario].[Scenario Type].&[IRSHORT]", _
"[Scenario].[Scenario Type].&[SPREADLOCK]", _
"[Scenario].[Scenario Type].&[IRVOLCORREL]", _
"[Scenario].[Scenario Type].&[InflationRate]", _
"[Scenario].[Scenario Type].&[SPREADOISIBOR]", _
"[Scenario].[Scenario Type].&[SPREADPAYSCASH]", _
"[Scenario].[Scenario Type].&[SPREADSWAPCASH]", _
"[Scenario].[Scenario Type].&[SPREADFUTURETITRE]", _
"[Scenario].[Scenario Type].&[DELTABASISINTERCUR]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR1]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR2]", _
"[Scenario].[Scenario Type].&[DELTABASISMONOCUR3]", _
"[Scenario].[Scenario Type].&[INFLATIONRATEVOLATILITY]", _
"[Scenario].[Scenario Type].&[AssetSwapInflationSpread]")
Sheets("PRODZoom").Select
End Sub