EXCEL :: Axes des abscisses dynamiques d'un graphique construit à partir d'un tableau croisé dynamique - Variante

[oguruma]

A partir de la 1ère solution présentée ici il est possible de récupérer les valeurs du TCD afin de construire le graphique sans l'associer au TCD. Cette solution ouvre les portes à plus de graphiques car en mode TCD certains ne sont pas autorisés et de plus vous accès aux séries de données en X et en Y. Cela permet donc de gérer les axes X et Y dynamiquement.

Cible de la démonstration

Le source de l'exemple est construit à partir de celui fourni dans le lien en introduction.

Une 3ème liste déroulante LC_PIVOT_03 a été créée avec ses propres composants RUBRIQUE_03 et LIBELLE_03

On passe toujours par un TCD afin de récupérer dynamiquement les données en X et les valeurs en Y dans un onglet spécifique nommé "LOV" pour List Of Values

Concernant la gestion dynamique des axes x et y :
on récupère les éléments du TCD de l'onglet LOV via ces deux formules :
- pour l'axe des X : =DECALER(ADR_DEBUT_CRITETERE;;;NBVAL(LOV!J2:J1000);1)
- pour l'axe des Y : =DECALER(LOV!K2;;;NBVAL(LOV!K2:K1000);1)

puis intervient dans le paramétrage de la série des données

l'astuce de la gestion dynamique est :

AXE_X et AXE_Y sont des plages nommées dynamiques

Le TCD se nomme TCD_03

Le code de macro MAIN() a été adapté afin de conserver les fonctionnalités de la 1ère version
Le voici dans son intégralité

Option Explicit
Sub MAIN(Optional sDumy As String)
    Dim vAppel As Variant
    Dim sType As String
    Dim sTCD As String
    
    On Error GoTo HANDLE_ERROR

    sType = TypeName(Application.Caller)
    Select Case sType
        Case "Range"
            vAppel = Application.Caller.Address
        Case "String"
            vAppel = Application.Caller
        Case "Error"
            vAppel = sDumy
        Case Else
            vAppel = sDumy
    End Select

    sTCD = Range(vAppel).Value
    Select Case vAppel
        Case "LC_PIVOT_FIELDS_01"
            Call CHANGE_PIVOT_FIELD_FROM_LIST(sTCD, "SELECT_LIBELLE")
        Case "LC_PIVOT_FIELDS_02"
            Call CHANGE_PIVOT_FIELD_FROM_LIST(sTCD, "SELECT_LIBELLE_02")
        Case "LC_PIVOT_FIELDS_03"
            Call CHANGE_PIVOT_FIELD_FROM_LIST_2(sTCD, "SELECT_LIBELLE_03", "LOV")
        Case Else
            ' NOP
    End Select

FIN:
    Exit Sub
    
HANDLE_ERROR:
    MsgBox "Erreur " & Err.Description & " - " & Err.Number
    Resume FIN
End Sub

Sub CHANGE_PIVOT_FIELD_FROM_LIST(hTCD As String, hLib As String)
    Dim wkActive As Worksheet
    Dim sFieldSelectFromList As String
    Dim sFieldSelectFromSeg As String
    
    Set wkActive = ActiveSheet
  
    sFieldSelectFromList = Range(hLib).Value

    Call CLEANUP_TCD(wkActive, hTCD)
    Call SET_PIVOT_FIELD(wkActive, hTCD, sFieldSelectFromList, xlRowField, 1)
    
End Sub

Sub CHANGE_PIVOT_FIELD_FROM_LIST_2(hTCD As String, hLib As String, hWK As String)
    Dim wkActive As Worksheet
    Dim sFieldSelectFromList As String
    Dim sFieldSelectFromSeg As String
    
    
    Set wkActive = ActiveWorkbook.Worksheets(hWK)
    sFieldSelectFromList = Range(hLib).Value

    Call CLEANUP_TCD(wkActive, hTCD)
    Call SET_PIVOT_FIELD(wkActive, hTCD, sFieldSelectFromList, xlRowField, 1)
    
End Sub


Sub CLEANUP_TCD(hWK As Worksheet, hTCD As String)
    Dim oPivotable As PivotTable
    Dim pfField As PivotField
    Dim pfFields As PivotFields
    
    Set pfFields = hWK.PivotTables(hTCD).PivotFields

    For Each pfField In pfFields
        If pfField.Orientation = xlRowField Then
            pfField.Orientation = xlHidden
        End If
    Next pfField
    
End Sub

Sub SET_PIVOT_FIELD(hWK As Worksheet, hTCD As String, hField As String, hOrientation As Integer, hPos As Integer)
    Dim oPivotable As PivotTable
    Dim pfField As PivotField
    Dim pfFields As PivotFields

    Set pfField = hWK.PivotTables(hTCD).PivotFields(hField)
    
    pfField.Orientation = hOrientation
    pfField.Position = hPos
    
End Sub