Evènements worksheet à partir du personal.xlsb

Le 19/03/2024 à 17:15

Bonjour,

Je travaille avec pas mal de classeurs en même temps et j'utilise beaucoup les filtres automatiques sur chacune des feuilles.

J'ai une macro qui me permet d'afficher le nom de toutes les colonnes filtrées dans la barre d'état:

capture 749

Le problème, c'est que je dois déclencher manuellement la macro chaque fois que je veux réactualiser la liste des filtres actifs.

J'aimerais donc automatiser le processus en utilisant les évènements.

Le but pour moi est donc que mon code soit placé dans mon classeur de macros personnelles "PERSONAL.xlsb"
Forcément, dans la liste déroulante, tout en haut de mes modules, je ne trouve aucun "WORKBOOK" ou "WORKSHEET": ça, c'est le premier écueil.

Ensuite, j'ai bien compris qu'il n'existe aucun évènement relatif aux filtres automatiques (deuxième écueil).
L'idée est donc de créer une formule un peu bateau dont le calcul sera réactualisé à chaque fois que j'applique ou retire un filtre quelle que soit la feuille, même si je passe en permanence d'une feuille à l'autre.
Je me suis dit que si j'insérais une fonction excel SOUS.TOTAL, sur ma première ligne et dans la dernière colonne + 1 et que j'applique un filtre, le résultat s'actualise.
Donc avec l'évènement "Worksheet.Calculate", je devrai être en mesure d'arriver à mes fins.

J'ai vu quelques sites (Les procédures événementielles) préconisant de créer un "gestionnaire d'événement au niveau de l'application Excel".

Bon déjà, avant que je comprenne qu'il fallait placer ce morceau de code dans un module de classe, j'ai pas mal bataillé et trouvé ça ailleurs.
Bref, voici ce que ça donne dans mon module de classe créé pour la circonstance:

Option Explicit

Public WithEvents appXls As Excel.Application

Private Sub appXls_SheetActivate(ByVal Sh As Object)

Dim FeuilleActive As Worksheet
Dim nbCol As Integer, nbRow As Integer
Dim LettreCol As String

Set FeuilleActive = ActiveSheet
nbCol = FeuilleActive.UsedRange.Columns.Count
nbRow = FeuilleActive.UsedRange.Rows.Count

'Insère la fonction SOUS.TOTAL si elle n'est pas présente
If Not FeuilleActive.Cells(1, nbCol).HasFormula Then
    LettreCol = Split(FeuilleActive.Cells(1, nbCol).Address, "$")(1)
    FeuilleActive.Cells(1, nbCol + 1).Formula = "=SUBTOTAL(3,A1:" & LettreCol & nbRow & ")" - 1
End If

End Sub

Private Sub appXls_SheetCalculate(ByVal Sh As Object)

Dim AutoFiltre As AutoFilter
Dim NomColonne As String
Dim ListeFiltres As String
Dim i As Integer

'Vérifie il y a au moins un filtre automatique actif sinon arrêt
If ActiveSheet.AutoFilterMode = False Then
Application.StatusBar = False
Exit Sub
End If

'Set le filtre automatique
Set AutoFiltre = ActiveSheet.AutoFilter

'Boucle pour déterminer tous les filtres actifs et le nom de la colonne associée
For i = 1 To AutoFiltre.Filters.Count
    If AutoFiltre.Filters(i).On Then
        NomColonne = AutoFiltre.Range.Cells(1, i).Value
        ListeFiltres = NomColonne & " | " & ListeFiltres
    End If
Next

'Suppression du pipe en fin de string
If Right(ListeFiltres, 2) = "| " Then
    ListeFiltres = Left(ListeFiltres, Len(ListeFiltres) - 3)
End If

'Affichage des colonnes filtrées dans la barre de STATUT (tout en bas d'excel)
If ListeFiltres = "" Then
    Application.StatusBar = False
    Else
    Application.StatusBar = "LES DONNÉES SONT FILTRÉES DANS " & ListeFiltres
End If

End Sub

Bref, une fois fait et bien je reste comme une poule qui a trouvé une brosse à dent.

J'ai créé 2 Sub:

  1. Une pour insérer la fonction SOUS.TOTAL dès qu'une feuille est activée (évènement appXls_SheetActivate)
  2. Une deuxième qui est censée s'activer dès que la fonction SOUS.TOTAL recalcule grâce à l'application ou suppression d'un filtre automatique (évènement appXls_SheetCalculate)

Mais lorsque j'applique un filtre sur ma feuille active, rien ne se passe.

Je ne maitrise déjà pas le passage de paramètres en temps normal mais là, dès qu'un évènement est créé, il y a "(ByVal Sh As Object)" qui est mis automatiquement et je ne sais pas quoi en faire.

Bref, je m'aventure un terrain inconnu et vos lumières seront les bienvenues pour me sortir de l'obscurité.
Merci d'avance pour vos contributions.
Cordialement.

1filtre-auto.xlsx (10.02 Ko)
Le 19/03/2024 à 18:56

Bonjour,

En fait ce qu'il te faut, c'est une macro complémentaire (tu dois pouvoir mettre le code dans le personal.xlb) qui détecte les recalculs. Tu dois ajouter une feuille par tableau filtré avec une fonction SOUS.TOTAL pointant sur le tableau en question :

=SOUS.TOTAL(103;Feuil1!D7:D14)

Un changement dans le filtre provoque le recalcul de la feuille. Celui-ci est détecté par un module de classe. Dans le classeur, le filtre provoque l'exécution de la macro "TaMacro" (dans le module de classe) :

Private Sub App_SheetCalculate(ByVal Sh As Object)
  TaMacro
End Sub

Dans le module standard, j'ai seulement mis :

Sub TaMacro()

End Sub

A toi de compléter...

Daniel

1workshet-events.xlsm (20.06 Ko)

Daniel

Le 20/03/2024 à 14:51

Bonjour Daniel,

Merci d'avoir pris du temps pour me répondre et vous penchez sur mon problème.
J'avoue avoir un peu bataillé parce que mes macros ne sont pas aussi évoluées.
J'ai réussi à mettre en œuvre votre solution mais je ne comprends pas tout.
Déjà, je ne sais pas trop ce qu'est un module de classe: c'est la première fois que j'en crée un.
Ensuite, je ne savais même pas qu'il y avait un Workbook et des sheets dans le personal.xlsb !
Je mets toujours toutes mes macros dans des modules ou userforms.
Pour en revenir à ce que vous m'avez gentiment fourni, je ne comprends pas trop à quoi sert la sub InitApp déclanchée par l'ouverture d'un classeur.
Quant à la Sub elle-même: ça me ferait plaisir d'avoir une ou 2 explications parce que c'est chaud à comprendre !

Sub InitApp()
    Set moAppEventHandler = New cAppEvents
    With moAppEventHandler
        Set .App = Application
    End With
End Sub

Enfin, pour des raisons qui me sont inconnues, en manipulant une feuille ou un autre classeur, les évènements ne fonctionnent plus.

J'ai créé un évènement App_SheetActivate dans le module de classe et si j'ouvre un classeur et que je change de feuille, tout fonctionne correctement.
Mais si j'ouvre un autre classeur ou que je modifie une feuille ou quelque chose d'autres, les évènements ne fonctionnent plus.
Peut-être pourrais-je vous fournir mon pesonnal.xlsb ?
Bonne fin d'après-midi.

Le 20/03/2024 à 15:43

Bonjour,

Je vais devoir t'avouer mon ignorance. En gros, un module de classe permet de créer de nouveaux objets. J'espère ne pas raconter trop d'âneries. Dans ce cas, il s'agit de créer une instance d'Excel ("Application") afin de pouvoir utiliser les évènements qui lui sont attachés. Pour de plus amples informations :

https://tissotemmanuel.developpez.com/articles/VBA/modules-de-classe/#LI

Je manipule très peu ces modules de classe (une fois par an ?) aussi, j'utilise des modèles que je modifie. La classe "Application" permet d'avoir accès aux classeurs ouverts, par exemple :

capture d ecran 2024 03 20 152410

Pour ce qui est du code de InitApp, il crée la variable "moAppEventHandler" qui est une instance de "cAppEvents" définie dans le module de classe :

Public WithEvents App As Application

Je suis infichu de recréer cette macro. C'est pourquoi, je me sers de modèles.

Sinon, fais-moi parvenir ton classeur et je regarderai.

Daniel

Rechercher des sujets similaires à "evenements worksheet partir personal xlsb"