Erreur VBA lorsqu'un membre n'appartient pas à la collection

[bond]

Dans une macro complémentaire, j'alimente une collection choisie des fichiers qui sont en ouverture (ici dummy.xls et tresorerie.xls) :

Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
If InStr(1, LCase(Wb.Name), "dummy") + _
InStr(1, LCase(Wb.Name), "tresorerie") >= 1 Then
WbNames.Add Item:=Wb.Name, Key:=Wb.Name
For WbName = 1 To WbNames.Count
MsgBox "Ouverture détectée du fichier : " & WbNames(WbName)
Next WbName
End If
End Sub​

Jusque là pas de problème. Toujours dans cette macro complémentaire, un autre sub doit faire une action lorsque sur les fichiers de cette collection, toujours pas de pb :

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim W As Workbook
For Each W In Workbooks
If W.Name = WbNames.Item(W.Name) Then
MsgBox "Action"
End If
Next W
End Sub​

Le problème : Comment éviter l'arrêt sur erreur vba pour les fichiers en dehors de cette collection ?

Merci d'avance.

Pour compléter ce post avec ma doc complète voici le sub complémentaire et préalable au fonctionnement général :

Option Explicit
Public WithEvents App As Application
Public WbName, WbNames As New Collection
Private Sub Workbook_Open()
' Activation de la macro complémentaire '
MsgBox "Hello ! Macro complémentaire ouverte et en arrière plan"
Set App = Application
End Sub​

 

[James007]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Re, Salut Pierrot 🙂

Bien vu ... ton astuce est une solution qui va fonctionner ...
Mais moi aussi je suis super surpris par ce code sans module de classe ...

A+
🙂

 

[bond]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Je confirme, ça tourne ! 😎
Je ne suis pas familier avec les modules de classe, sans y passer 1 milliard d'heures, si t'as une suggestion par ce biais...

En tout cas, merci à vous, James et Pierrot de vous être pencher sur ce cas.

 

[Pierrot93]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Re,

regarde le fil ci-dessous,
https://www.excel-downloads.com/threads/fichier-qui-doit-etre-le-seul-a-pouvoir-souvrir.155391/
un code fait pour Michel, il contrôle l'ouverture de nouveaux classeurs via un module de classe, tu devrais pouvoir adapter assez facilement....

@+

 

[bond]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Ok je regarde...
Mais dans ce cas, j'imagine que ça change le code que tu viens de me passer 😕

 

[Pierrot93]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Re,

oui, mais cela peut permettre d'avoir une autre approche.... A voir...

 

[bond]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Alors si je m'inspire du fil, je réorganise le code comme ça :
Dans ThisWorkbook

Option Explicit
Private Sub Workbook_Open()
    Set z.App = Application
End Sub

Dans Module1

Option Explicit
Public z As New Classe1
Public WbName, WbNames As New Collection

Dans Classe1

Option Explicit
Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook)
    If InStr(1, LCase(Wb.Name), "dummy") + _
        InStr(1, LCase(Wb.Name), "tresorerie") >= 1 Then
            WbNames.Add Item:=Wb.Name, Key:=Wb.Name
            For WbName = 1 To WbNames.Count
                MsgBox "Ouverture détectée du fichier : " & WbNames(WbName)
            Next WbName
    End If
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim W
If WbNames.Count > 0 Then
    For Each W In WbNames
        If Sh.Parent.Name = W Then
            MsgBox "Action"
        End If
    Next W
End If
End Sub

Mais je ne comprends pas ce que ça change... 😕
et je ne sais pas ce que ça permet de faire en plus ou en mieux (?)

 

[Pierrot93]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Re,

perso verrais cela ainsi :
dans thisworkbook :

Option Explicit
Private Sub Workbook_Open()
    Set z.App = Application
End Sub

dans un module standard :

Option Explicit
Public z As New Classe1

dans le module de classe :

Option Explicit
Public WithEvents App As Application
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Parent.Name Like "dummy*" Or Sh.Parent.Name Like "tresorerie*" Then MsgBox "Action"
End Sub

Maintenant, à voir la finalité de tout ceci...

 

[bond]

Re : Erreur VBA lorsqu'un membre n'appartient pas à la collection

Même conclusion de MJ13 :

Pierrot, tu es un génie !

Voilà, maintenant je peux identifier l'ouverture de certains fichiers (utilisés sur un réseau), déceler l'activité -voire l'inactivité- sur ceux ci et informer l'utilisateur afin qu'il ne bloque pas le fichiers pour d'autres personnes ; mais ça c'est la suite du code que je vais poser.

Mille Mercissss, encore.