Discussion :

[Arno76_68]

Bonjour,

Je vais essayé d'être le plus précis possible...
Classeur #1 = fichier xls où je ne veux/peux pas accéder au code VBA
Classeur #2 : mon fichier, où je veux détecter un changement de valeur de certaines cellules sur une feuille portant toujours le même nom.

Ce que j'ai déjà fait dans mon fichier (Workbook_Open) :
1) connaitre le nom de mon fichier => my_filename = ThisWorkbook.Name
2) Connaitre le nom du classeur #1 également ouvert :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
For Each W In Workbooks
If W.Name <> my_filename Then
match_filename = W.Name
End If
Next W

3) Copier les donner du classeur #1 et les coller les liens dans une feuille "Live_data" de mon classeur #1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Workbooks(match_filename).Activate
Call SHEET_EQUIPE_MATCH
Range("B1:AB52").Select
Selection.Copy
Workbooks(my_filename ).Activate
Sheets("Live_data").Select
Range("A1").Select
ActiveSheet.Paste Link:=True

4) Mon classeur "Feuil1", j'ai lié manuellement via formule (=Live_data!E10 (etc...)), chaque cellule avec les cellules correspondantes de la feuille "Live_data"

Du coup, ça fonctionne : à chaque changement de valeur du fichier #1, mes cellules de la "Feuil1" ont bien les valeurs souhaitées.
Je veux générer des ouvertures d'Userform selon les valeurs de ces cellules, si la valeur de cette cellule est égale à un certain nombre, mais uniquement la première fois.
Cela fonctionne également, via l'évenement "Worksheet_Calculate"...mais pour se faire j'ai dû créé une variable type Integer pour chaque cellule que je mets à 1 dès que la valeur à ouvert l'Userform, et qu'il ne lance plus l'userform au prochain changement d'une autre cellule.

Je souhaiterais alléger et simplifier tout ça...dans l'idéal je pensais utiliser l'évènement "Change" en utilisant Target...mais comme j'ai des formules dans mes cellules ("=Live_data!E10") il n'y a pas de target change...et du coup cela ne fonctionne pas.

D'où ma question : comment puis-je détecter un changement de valeur d'un autre classeur ?
J'espère que tout cette explication reste compréhensible...

Merci d'avance à ceux qui prendrons le temps de me répondre.

[deedolith]

Salut,

Pour cela, tu dois déclarer, au sein d'une classe, une référence vers le classeur ou feuille externe via le mot clef WithEvents.
Bien sûre ne pas oublier d'instancier la classe, et faire attention à la durée de vie de cette dernière (si l'instance est détruite, plus d'evennement).
Exemple avec un classeur (le principe est identique avec une feuille):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
'// Module de classe: Classeur
Public WithEvents Workbook As Excel.Workbook
 
Private Sub Workbook_Activate()
    '// Capture de l'evennement Activate du classeur
 
    '// --------------------------------
    '// Code quelconque
    '// --------------------------------
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
'// Module Standard: Main
Private ExternalWorkbook As Classeur
Public Sub Main()
    Set ExternalWorkbook = New Classeur
    Set ExternalWorkbook.Workbook = Application.Workbooks.Open "C:\...................."
End Sub

Aller plus loin:
L'approche ci-dessus est extrêmement naïve et ne respecte pas les bonnes pratiques, entre autre la Loi de Demeter: https://fr.wikipedia.org/wiki/Loi_de_D%C3%A9m%C3%A9ter et le principe d'encapsulation
La classe Classeur devant garder le contrôle de ses variables internes, elle n'a aucune raison de les exposer publiquement.
Avec un langage supportant pleinement les principes objet, cela ne pose pas de problème car on peut écrire plusieurs versions du constructeur (surcharge de fonction), mécanique non disponible en VBA.

Mais tout n'est pas perdu
On peut écrire un pseudo constructeur avec en paramètre une référence vers le classeur externe, ainsi qu'une fonction en charge d'instancier la classe Classeur.
Par soucis de clarification, on placera cette dernière dans une module appelé Factory (Fabrique):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
'// Module de classe: Classeur
'// Note que la référence vers le classeur externe est maintenant privée
Private WithEvents mWorkbook As Excel.Workbook
 
    '// Pseudo constructeur
    '// Initialisation des membres privés
Public Sub Create(ByVal Workbook As Excel.Workbook)
    Set mWorkbook = Workbook
End Sub
 
Private Sub mWorkbook_Activate()
    '// Capture de l'evennement Activate du classeur
 
    '// --------------------------------
    '// Code quelconque
    '// --------------------------------
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
'// Module Standard: Factory
 
    '// Fonction prenant en charge la construction d'un objet de type Classeur
Public Function CreateClasseur(ByVal Workbook As Excel.Workbook) As Classeur
    Dim Instance As Classeur
    Set Instance = New Classeur
 
    Instance.Create Workbook
    Set CreateClasseur = Instance
Exit Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
'// Module Standard: Main
Private ExternalWorkbook As Classeur
Public Sub Main()
    Dim Wb As Excel.Workbook
    Set Wb = Application.Workbooks.Open "C:\...................."
 
        '// On se content d'instancier un objet de type Classeur via la fonction CreateClasseur.
        '// On préfixe avec le nom du module Factory pour apporter plus de contexte et de lisibilité
        '// L'initialisation de l'objet ne nous concerne pas
    Set ExternalWorkbook = Factory.CreateClasseur(Wb)
End Sub

Certes, cette approche n'interdit pas d'appeler plusieurs fois le pseudo constructeur,
elle à au moins le mérite de restreindre l'accès aux variables membres de la classe puisque ces derniers sont privés (ce qui sera détecté à la compilation),
ainsi qu'apporter plus de contexte, plus de lisibilité, ce qui facilite la compréhension.