XL 2010 Aide actualisation TCD avec Combobox

[safranien]

Bonjour à tous,

cela fait des jours que je cherche et que je ne trouve rien pour résoudre mon problème. Je ne peux joindre mon fichier car même épuré au maximum, il reste trop volumineux (1,3 Mo) et on ne peut pas joindre de zip (ce qui est bien dommage). Je vais donc tenter de décrire au mieux.

Soit mon fichier comportant :

onglet Calculs = récupération de datas sur un serveur d'entreprise et plusieurs calculs à partir de ces données
onglet BDD = liste des valeurs que l'on retrouve dans le menu déroulant présent dans l'onglet Graphs
onglet Graphs =
_ un menu déroulant en E3:ER3 (cellules fusionnées) ayant pour nom de liste =ListeSites2 provenant de l'ongolet BDD
_ plusieurs graphiques construits sur les calculs de l'onglet Calculs
_ un TCD reprenant les valeurs présentes dans l'onglet Calculs (pouvant aller jusqu'à 52 000 lignes)

Lorsque je change de nom via ce menu, le fichier récupère les nouvelles datas liées au nouveau nom, tous les calculs se font, les graphiques se mettent à jour et mon TCD également via le code ci-dessous présent dans la feuille Graphs

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("E3")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.PivotTables("Tableau croisé dynamique1").RefreshTable
Application.EnableEvents = True
End If
End Sub

La taille de la police dans le menu déroulant est petite (la feuille est en Zoom 80%) et j'ai voulu mettre une ComboBox avec cellule liée = $E$3 et liste de données = ListeSites2. Et je constate deux problèmes :
_ le 1er est que depuis l'ajout de cette Combobox, je vois que les calculs se lancent 4 fois à la place d'une seule fois sans la Combobox
_ le TCD ne se met pas à jour. Je suis obligé de le faire manuellement, ce qui relance encore 3 ou 4 fois les calculs.

Pouvez vous me guider sur la manière de faire pour obtenir le résultat voulu à savoir :
_ choix du site via la Combobox
_ calculs se réalisent (le fichier est en Option de Calcul Automatique) en 1 seul fois
_ le TCD se met à jour

En espérant que vous pourrez m'aider.

Merci

 

[job75]

Bonjour safranien,

on ne peut pas joindre de zip

Bien sûr que si, s'il fait moins de 1 Mo, sinon allégez le fichier.

A+

 

[safranien]

Bonjour Job75

ravi de vous lire. Effectivement, je viens de joindre le fichier compressé en ZIP. Le site me refuse les fichiers 7zip par contre.

En solution palliative, dans le code de la feuille Graphs, j'ai ce code pour zoomer la feuille à 110% afin que le menu déroulant soit plus gros

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20160530
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 85
    If Target.Validation.Type = xlValidateList Then xZoom = 110
    
LZoom:
    ActiveWindow.Zoom = xZoom
    
End Sub

Et ensuite ce code pour la mise à jour du TCD après détection de changement de valeur dans le TCD (le Range("ES3").Select sert à revenir au zomm original car il ne revient pas de lui même avec le précédent code) :

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("E3")) Is Nothing Then

Range("ES3").Select
Application.EnableEvents = False
ActiveSheet.PivotTables("Tableau croisé dynamique1").RefreshTable
Application.EnableEvents = True

End If
End Sub

 

[job75]

Où est le TCD ?

 

[safranien]

dans l'onglet Graphs, B10 (Date Courte), là où il y a les cellules avec MFC en jaune, rouge, vert

 

[job75]

Au lieu du code dans la feuille Graphs placez dans ThisWorkbook :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Feuil5.PivotTables(1).RefreshTable
Application.EnableEvents = True
End Sub

 

[safranien]

merci mais ça ne fonctionne pas plus. Ci-joint votre fichier dans lequel j'ai ajouté une liste déroulante ComboBox. En passant d'un site à un autre, le TCD ne s'actualise pas et je dois l'actualiser manuellement.

 

[job75]

Chez moi les feuilles Calculs et Graphs s'actualisent quand on modifie le site par la liste de validation en E3.

Je ne vois pas l'intérêt de la ComboBox.

 

[safranien]

en passant par la comboxbox les feuilles s'actualisent mais pas le tableau croisé dynamique et son contenu. Chez vous si?
L'intérêt de la combobox est d'offrir un confort de lecture et de navigation en affichant les noms en plus gros que ce que ne les affiche la liste de validation. qui est tout petit.

 

[job75]

Chez moi le TCD ne se met pas à jour de la même manière avec la ComboBox.

 

[safranien]

Voilà tout mon soucis pour lequel je cherche une aide pour actualiser le TCD automatiquement après avoir choisi une valeur dans la combo box. (et une fois les calculs finis)
Ne peut on pas lier une action sur la combo box avec une macro qui viendra actualiser les TCD après les calculs ?

 

[job75]

Voyez cette macro dans le code de la feuille Graphs :

Private Sub Combobox1_Change()
[E3] = ComboBox1
End Sub

 

[safranien]

Cela semble fonctionner. J'ai fait une copie de mon fichier de travail et j'y ai inséré les mêmes codes que ce fichier d'échange, en supprimant tous les autre codes, afin de comparer à "codes" équivalents.
Mon fichier de travail comporte beaucoup plus de données à calculer. Après ajout de la ComboBox, les données se calculent et le TCD s'actualise. Cependant, le temps de traitement s'est considérablement allongé comparé à mon fichier de travail que j'utilise avec le menu déroulant, comme je le mentionne dans mon premier post. Avec la ComboBox, je vois qu'excel lance 4 fois les calculs avant de se décider à actualiser le TCD. Le process calculs + actualisation du TCD prend 22 secondes.
Dans mon fichier de travail avec menu déroulant, le process complet prend un peu moins de 9 secondes.

Avez vous une idée du pourquoi de ces multiples relances de calculs quand on passe par la ComboBox?

 

[job75]

Avez vous une idée du pourquoi de ces multiples relances de calculs quand on passe par la ComboBox?

Le TCD est actualisé 2 fois , pour l'éviter effacez la propriété LinkedCell de la ComboBox.

 

[safranien]

Il y a du mieux, il n'y a plus que 3 calculs ... Le calcul du classeur se lance deux fois, puis actualisation du TCD puis de nouveau un calcul.
Avec la liste déroulante de mon autre fichier, il y a un seul calcul et ça se finit avec l'actualisation du TCD.
Je n'y comprends rien.