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.