Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

[VBA] Mise à jour de TCD avec feuilles protégées sur un gros classeur avec calcul manuel

6 réponses
Avatar
HD
Bonjour,

J'ai un souci avec un gros classeur contenant beaucoup de données sur lequel
j'ai dû mettre le calcul en manuel.

J'ai les données sources situées sur 4 feuilles ("FeuilleSource1",
"FeuilleSource2", "FeuilleSource3", "FeuilleSource4") avec tableaux et
etc... qui sont regroupées via des liens sur une feuille de synthèse
("Temps"). A partir de cette feuille de synthèse un Tableau Croisé Dynamique
(TCD qui se nomme "TCDHMois") a été réalisé. Mon souci est que lorsque je
modifie une donnée sur l'une des 4 feuilles sources le TCD ne se met pas à
jour. Avec le calcul manuel désactivé je sais que si une donnée est modifiée
sur l'une des 4 feuilles sources je dois d'abord recalculer la feuille de
synthèse pour ensuite mettre à jour le TCD. Mais... en VBA cela ne
fonctionne pas. A savoir également que les feuilles de données sources ainsi
que la feuille de synthèse sont protégés. Voici ma macro de mise à jour:
----------------------------------
Global BModif as Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Select Case Sh.Name
Case "FeuilleSource1", "FeuilleSource2", "FeuilleSource3",
"FeuilleSource4"
BModif = True
End Select
End Sub

Private Sub Workbook_Open()
On Error Resume Next
BModif = False

Application.Calculation = xlManual
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
if Sh.Name="H.Mois" then WSA_HMois
End Select
End Sub

Sub WSA_HMois()
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.StatusBar = "Veuillez patienter..."
If BModif = True Then
Sheets("Temps").Select
DeProt
Application.Calculate
Sheets("H.Mois").Select
DeProt
Range("A5").Select
ActiveSheet.PivotTables("TCDHMois").PivotCache.Refresh
ActiveWorkbook.RefreshAll
DoEvents
Sheets("Temps").Select
Prot
Sheets("Nb.Mois").Select
BModif = False
End If
Application.StatusBar = ""
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
----------------------------------

A savoir que les fonctions Deprot et Prot servent à protéger et déprotéger
une feuille.

Cordialement,

@+
HD

6 réponses

Avatar
MichD
Bonjour,

Dans le thisworkbook de du projetVBA de ton classeur,
copie cette procédure :
Tu enregistres et fermes ton fichier. Lorsque tu ouvres
ton fichier as-tu toujours le même problème?

'-------------------------------------------
Private Sub Workbook_Open()
Dim Sh As Worksheet

For Each Sh In ThisWorkbook.Worksheets
Select Case Sh.Name
Case Is = "FeuilleSource1", "FeuilleSource2", _
"FeuilleSource3", "FeuilleSource4"
Sh.Protect Password:="toto", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
UserInterFaceOnly:=True
End Select
Next
End Sub
'-------------------------------------------



MichD
---------------------------------------------------------------
"HD" a écrit dans le message de groupe de discussion :
l5infm$25ts$

Bonjour,

J'ai un souci avec un gros classeur contenant beaucoup de données sur lequel
j'ai dû mettre le calcul en manuel.

J'ai les données sources situées sur 4 feuilles ("FeuilleSource1",
"FeuilleSource2", "FeuilleSource3", "FeuilleSource4") avec tableaux et
etc... qui sont regroupées via des liens sur une feuille de synthèse
("Temps"). A partir de cette feuille de synthèse un Tableau Croisé Dynamique
(TCD qui se nomme "TCDHMois") a été réalisé. Mon souci est que lorsque je
modifie une donnée sur l'une des 4 feuilles sources le TCD ne se met pas à
jour. Avec le calcul manuel désactivé je sais que si une donnée est modifiée
sur l'une des 4 feuilles sources je dois d'abord recalculer la feuille de
synthèse pour ensuite mettre à jour le TCD. Mais... en VBA cela ne
fonctionne pas. A savoir également que les feuilles de données sources ainsi
que la feuille de synthèse sont protégés. Voici ma macro de mise à jour:
----------------------------------
Global BModif as Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Select Case Sh.Name
Case "FeuilleSource1", "FeuilleSource2", "FeuilleSource3",
"FeuilleSource4"
BModif = True
End Select
End Sub

Private Sub Workbook_Open()
On Error Resume Next
BModif = False

Application.Calculation = xlManual
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
if Sh.Name="H.Mois" then WSA_HMois
End Select
End Sub

Sub WSA_HMois()
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.StatusBar = "Veuillez patienter..."
If BModif = True Then
Sheets("Temps").Select
DeProt
Application.Calculate
Sheets("H.Mois").Select
DeProt
Range("A5").Select
ActiveSheet.PivotTables("TCDHMois").PivotCache.Refresh
ActiveWorkbook.RefreshAll
DoEvents
Sheets("Temps").Select
Prot
Sheets("Nb.Mois").Select
BModif = False
End If
Application.StatusBar = ""
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
----------------------------------

A savoir que les fonctions Deprot et Prot servent à protéger et déprotéger
une feuille.

Cordialement,

@+
HD
Avatar
HD
Bonjour MichD,

Je ne connaissais pas l'option UserInterFaceOnly... intéressant...

Dans le thisworkbook de du projetVBA de ton classeur,
copie cette procédure :
Tu enregistres et fermes ton fichier. Lorsque tu ouvres
ton fichier as-tu toujours le même problème?


Malheureusement cela n'a rien changé à mon problème.

Je me demande si mon problème ne viendrait pas également du nombre
conséquent de liens... Exemple, rien que pour les formules de cellules avec
la fonction INDIRECT j'en ai dans les 768000... d'où la lourdeur du
fichier... (enregistré en xlsb pour réduire la taille).

@+
HD
Avatar
HD
Je me demande si mon problème ne viendrait pas également du nombre
conséquent de liens... Exemple, rien que pour les formules de cellules
avec la fonction INDIRECT j'en ai dans les 768000... d'où la lourdeur du
fichier... (enregistré en xlsb pour réduire la taille).


L'utilisation de liens INDIRECT est très pratique mais... je me demande si
il n'y a pas plus rapide, moins lourd, pour Excel ?

@+
HD
Avatar
MichD
Pourquoi ne pas copier dans une feuille, toutes les données
de tes feuilles sources dans une feuille "résultat" à chaque fois
que tu veux avoir une nouvelle compilation de ton TDC.

Lorsque la copie des données vers cette feuille est faite, tu ajoutes
une ligne de code pour modifier la source des données de ton TDC.

Tu peux même utiliser l'événement suivant dans le module de la
feuille contenant le TDC
'--------------------------------
Private Sub Worksheet_Activate()
'Copie des données vers la feuille Résultat
'Mise à jour de la plage du TDC
End Sub
'--------------------------------

Par conséquent, à chaque fois que tu sélectionneras la feuille de ton TDC,
ce dernier sera à jour de même que sa plage source.


MichD
---------------------------------------------------------------
"HD" a écrit dans le message de groupe de discussion :
l5iunp$2887$

Bonjour MichD,

Je ne connaissais pas l'option UserInterFaceOnly... intéressant...

Dans le thisworkbook de du projetVBA de ton classeur,
copie cette procédure :
Tu enregistres et fermes ton fichier. Lorsque tu ouvres
ton fichier as-tu toujours le même problème?


Malheureusement cela n'a rien changé à mon problème.

Je me demande si mon problème ne viendrait pas également du nombre
conséquent de liens... Exemple, rien que pour les formules de cellules avec
la fonction INDIRECT j'en ai dans les 768000... d'où la lourdeur du
fichier... (enregistré en xlsb pour réduire la taille).

@+
HD
Avatar
HD
Pourquoi ne pas copier dans une feuille, toutes les données
de tes feuilles sources dans une feuille "résultat" à chaque fois
que tu veux avoir une nouvelle compilation de ton TDC.


Cela aurait pu être intéressant. Mais le souci est que mes feuilles sources
sont organisées d'une manière "non linéaire", totalement différente de ce
que l'on doit avoir dans un tableau Excel permettant ensuite d'avoir un TDC.

Exemple pour un jour :
Lundi
h.début matin h.fin matin événement
h.début aprèm h.fin aprèm

J'ai en en-tête de ligne une personne et en colonne les jours du lundi au
dimanche. Les colonnes des jours étant subdivisés en 2 colonnes heure début,
heure fin pour le matin sur une ligne, sur une ligne en dessous avec heure
début et heure fin pour l'après midi et une colonne à côté avec les deux
cellules fusionnées où apparait l'événement (CP, RTT, absence, etc...).

En fait je me suis retrouvé avec un tableau compliqué avec des données pas
directement traitable par Excel pour des filtres ou des TCD... du coup j'ai
dû refaire un tableau organisé sur une autre feuille avec toutes les
liaisons vers les feuilles sources...

@+
HD
Avatar
MichD
Essaie en plaçant ce code dans le thisworkbook du projetVBA du fichier
Assure-toi que le nom des feuilles dans les procédures est conforme
à la réalité de ton application.

'------------------------------
Private Sub Workbook_Activate()
Application.Calculation = xlCalculationManual
End Sub
'------------------------------
Private Sub Workbook_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub
'------------------------------
Private Sub Workbook_Open()
Dim Sh As Worksheet

For Each Sh In ThisWorkbook.Worksheets
Select Case Sh.Name
Case Is = "FeuilleSource1", "FeuilleSource2", _
"FeuilleSource3", "FeuilleSource4"
Sh.Protect Password:="toto", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
UserInterFaceOnly:=True
End Select
Next
End Sub
'------------------------------
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Select Case Sh.Name
Case Is = "FeuilleSource1", "FeuilleSource2", _
"FeuilleSource3", "FeuilleSource4"
Sh.Calculate
Worksheets("NomFeuilleSynthèse").Calculate
End Select
End Sub
'------------------------------



MichD
---------------------------------------------------------------
"HD" a écrit dans le message de groupe de discussion :
l5j28c$29dm$

Pourquoi ne pas copier dans une feuille, toutes les données
de tes feuilles sources dans une feuille "résultat" à chaque fois
que tu veux avoir une nouvelle compilation de ton TDC.


Cela aurait pu être intéressant. Mais le souci est que mes feuilles sources
sont organisées d'une manière "non linéaire", totalement différente de ce
que l'on doit avoir dans un tableau Excel permettant ensuite d'avoir un TDC.

Exemple pour un jour :
Lundi
h.début matin h.fin matin événement
h.début aprèm h.fin aprèm

J'ai en en-tête de ligne une personne et en colonne les jours du lundi au
dimanche. Les colonnes des jours étant subdivisés en 2 colonnes heure début,
heure fin pour le matin sur une ligne, sur une ligne en dessous avec heure
début et heure fin pour l'après midi et une colonne à côté avec les deux
cellules fusionnées où apparait l'événement (CP, RTT, absence, etc...).

En fait je me suis retrouvé avec un tableau compliqué avec des données pas
directement traitable par Excel pour des filtres ou des TCD... du coup j'ai
dû refaire un tableau organisé sur une autre feuille avec toutes les
liaisons vers les feuilles sources...

@+
HD