Discussion :

[Excel'air]

Bonjour à tous,

J'aurais voulu savoir si il était possible d'exporter des procédures de feuille (add-in) et d'ensuite pouvoir les appeler dans mon module pour pouvoir les exécuter sur les pages que je vais générer.

De plus, est ce que cette méthode est préférable à celle ou je générerais directement dans mon module avec un code un peu de ce genre :

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
18
19
20
Sub AddProcedureToModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character
 
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
 
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub SayHello()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
    End Sub

Source : http://www.cpearson.com/excel/vbe.aspx

Si vous avez d'autres méthodes ou tuto pour faire ça, je suis preneur.

Merci!

[Pierre Fauconnier]

Salut.

Qu'appelles-tu des procédures de feuille? Des procédures évènementielles placées sur une feuille, genre Worksheet_Change()?

Si c'est cela, tu retrouves ces procédures au niveau du classeur. Elles portent pratiquement le même nom et reçoivent en argument l'objet Sheet manipulé.

[Excel'air]

Bonjour Pierre,


En fait je crée mes pages ainsi que des ComboBox dans 'Module 1'.
Ensuite j'ai des procédures événementielles sur des ComboBox, mais mes procédures et arguments ne sont reconnus que dans mes feuilles.
Voici un aperçu de mes procédures :

Je n'ai pas retrouvé de procédures sur les ComboBox dans le classeur.
Je peux éventuellement mettre en ligne mon projet si tu préfères.

Merci pour ta réponse !

[Pierre Fauconnier]

Dans le module thisworkbook, tu crées une évènementielle sur le SelectionChange d'une feuille, et dedans, tu places ton code en remplaçant me par Sh (Sh pointe vers la feuille dans laquelle ta sélection vient de changer, autrement dit ta feuille active

Code :

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

1
2
3
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  sh.combobox1...
End Sub

Perso, je ne mets jamais de code métier dans les évènements, je crée mes procédures métier dans des modules standards, en respectant la norme qu'une procédure/fonction a une et une seule responsabilité. Ainsi, si je dois effectuer des traitements différents, je crée simplement les appels aux procédures spécifiques dans mon évènement. En outre, cela permet de généraliser le traitement.

Code :

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

1
2
3
sub MaProc1(sh as worksheet, Target as range)
...
...

Et dans l'évènement

Code :

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

1
2
3
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  MaProc1 sh, target
  ...

[Excel'air]

Merci pour ta méthode qui a l'air bien plus propre que la mienne.

Donc si j'ai bien compris mon projet devrait ressembler à quelque chose comme ça :


L'appel semble bien fonctionner, j'ai juste une erreur

Membre de méthode ou de données introuvable

sur ma ComboBox1.
J'avais cette erreur lorsque j'oubliais de créer mes ComboBox au préalable, or, j'ai vérifié, donc j'imagine que le souci viens du fait que mon code ne reconnaît pas la ComboBox mais je ne vois pas comment remédier à ça.

Est ce qu'il est possible de passer la combobox en paramètre pour avoir quelque chose comme ça

Code :

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

1
2
3
4
 
Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
Call Worksheet_SelectionChange(sh, Target, Combobox1)
End Sub

Pour qu'ensuite mon code reconnaisse ma CmbBox

Code :

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

 Sub Worksheet_SelectionChange(sh As Worksheet, ByVal Target As Range, ComboBox As Object)

[Pierre Fauconnier]

Le Call est inutile. Tu peux directement utiliser le nom de la procédure.

Code :

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

Worksheet_SelectionChange sh, target

Vu ton code, tu dois évidemment avoir un combobox nommé Combobox1. Mais tu peux effectivement ajouter l'argument qui passe le combobox, par exemple comme ceci.

Code :

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

sub Worksheet_SelectionChange(sh as worksheet, Target as range, cbo as msforms.combobox)

Tu peux aussi utiliser le bloc With pour alléger ton code:

Code :

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

1
2
3
4
with cbo
  .width = ...
  .height = ...
end with

[Excel'air]

Je pense que tu as répondu à mon problème initial.

Juste une petite question, je bloque, faut-il initialiser cbo dans le module ThisWorkbook ?
J'ai essayé comme ça :

Code :

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

Public WithEvents MesCombobox As MSForms.ComboBox

et j'ai une erreur

Type d'argument ByRef incompatible

Aussi, j'ai un doute avec mes procédures de contrôle (ComboBox1_Change, ComboBox1_DblClick... ). Comment sont elles censés fonctionner ? Je n'ai pas besoin de spécifier la feuille active ?

[Pierre Fauconnier]

J'ai été incomplet et imprécis dans ma réponse. Désolé.

Vu que tu passes l'objet sh, tu n'as pas besoin de passer le combobox, puisqu'il est "sur" la feuille que tu passes. Sur la feuille, il est placé en tant que OLEObject, qui expose la propriété Object qui, elle, pointer vers le "vrai objet".



Dans le module ThisWorkbook, tu places

Code :

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

1
2
3
Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
  Worksheet_SelectionChange sh, Target
End Sub

Dans la procédure "standard", tu places ceci:

Code :

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

1
2
3
4
5
6
7
8
Sub Worksheet_SelectionChange(sh As Worksheet, Target As Range)
  Dim cbo As msforms.ComboBox
 
  Set cbo = sh.OLEObjects("combobox1").Object
  With cbo
 
  End With
End Sub

Bien entendu, cela suppose que tu aies un combobox nommé Combobox1 sur chaque feuille de ton classeur. Il serait donc nécessaire de ne se brancher sur Worksheet_SelectionChange que pour certaines feuilles (à tester avec une condition, par exemple sur le nom de la feuille) dans l'évènement de ThisWorkbook, ou encore, mais j'aime moins, de réaliser cette discrimination dans la procédure standard.

[Excel'air]

Parfait, ça fonctionne !

J'ai juste les procédures de contrôles sur les Combobox qui ne fonctionnent pas, je vais tenter de trouver une solution.

En tout cas beaucoup tu as fais le plus gros, je met le sujet .

[Excel'air]

Salut Pierre,

Désolé de revenir à la charge, j'essayes toujours de faire fonctionner mes procédures de contrôles. J'ai utilisé la même méthode que tu m'as donné.

Dans ThisWorkbook j'ai placé

Code :

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

1
2
3
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ComboBox1_Change Sh, Target
End Sub

Et dans mon module j'ai mis

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
Sub ComboBox1_Change(Sh As Worksheet, ByVal Target As Range)
 
 Dim cbo As MSForms.ComboBox
 Set cbo = Sh.OLEObjects("Combobox1").Object
 cbo = Target
 If cbo <> "" And IsError(Application.Match(cbo, a, 0)) Then
   Set d1 = CreateObject("Scripting.Dictionary")
   tmp = UCase(cbo) & "*"
   For Each c In a
     If UCase(c) Like tmp Then d1(c) = ""
   Next c
  cbo.List = d1.keys
   cbo.DropDown
   [e2] = cbo
 End If
   ActiveCell.Value = cbo
End Sub

Le code tourne je n'ai pas d'erreurs, mais le contrôle ne fonctionne, tu aurais une idée ?

Merci

PS : J'hésitais à ouvrir une nouvelle discussion, mais je pense que c'est un prolongement de mon problème initial.

[Pierre Fauconnier]

Salut.

Dans ton code, je n'ai pas vu où tu déclarais a (ni d'autres variables d'ailleurs) et surtout ce que tu affectais à a.

Tu aurais intérêt à déclarer toutes tes variables, et à forcer cette déclaration on plaçant la ligne Option Explicit au début de ton module.

Pour avoir cette ligne automatiquement sur tes nouveaux modules, tu peux aller dans Outils/options/Editeur et cocher Déclaration des variables obligatoire. Cela t'aidera à avoir un code plus propre

PS: J'ai rendu la discussion non résolue

[Excel'air]

Alors j'ai déclaré a en tant que que variable globale dans Module 2 et je l'ai affecter comme ceci

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
18
19
20
21
22
23
24
25
26
Option Explicit
Dim a()
 Sub Worksheet_SelectionChange(Sh As Worksheet, ByVal Target As Range)
Dim cbo As MSForms.ComboBox
Set cbo = Sh.OLEObjects("Combobox1").Object
  If Not Intersect(Range("B:W"), Target) Is Nothing And Target.Count = 1 Then
  
    a = Sheets("data").Range("Tableau3").Value     
cbo = Target
    With cbo
    .List = a
    .Height = Target.Height + 3
    .Width = Target.Width
    .Top = Target.Top
    .Left = Target.Left
    
    .Visible = True
    .Activate
    
    End With
    'If Target <> "" Then SendKeys "{esc}"
    'Me.ComboBox1.DropDown
  Else
    cbo.Visible = False
  End If
End Sub

J'ai aussi déclaré ce qui me manquait de cette manière, toujours pas de changements

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
18
19
20
21
22
Sub ComboBox1_Change(Sh As Worksheet, ByVal Target As Range)
 
 Dim cbo As MSForms.ComboBox
 Dim d1 As Object
 Dim tmp As String
 Dim c As Variant
 
 a = Sheets("data").Range("Tableau3").Value
 Set cbo = Sh.OLEObjects("Combobox1").Object
 cbo = Target
 If cbo <> "" And IsError(Application.Match(cbo, a, 0)) Then
   Set d1 = CreateObject("Scripting.Dictionary")
   tmp = UCase(cbo) & "*"
   For Each c In a
     If UCase(c) Like tmp Then d1(c) = ""
   Next c
  cbo.List = d1.keys
   cbo.DropDown
   [e2] = cbo
 End If
   ActiveCell.Value = cbo
End Sub

[Excel'air]

J'ai peut être trouvé l'origine du problème :

Après quelques recherches, j'ai vu que l'événement Workbook_SheetChange ne détectait pas les changements dans un ComboBox.
Je ne vois aucun autre contrôle d'événements qui pourrait faire l'affaire

[Pierre Fauconnier]

Pourquoi déclarer a comme global à l'extérieur de la procédure?
Pourquoi passer par un tableau? Tu pourrais utiliser directement la plage dans le match (If cbo <> "" And IsError(Application.Match(cbo, range("tableau3"), 0)) Then)
Es-tu certain de devoir rechercher sur Tableau3? Que représente cette range? Un tableau structuréde plusieurs colonnes? une plage multicolonnes? Une seule colonne?


Pour compléter sur ton évènement Combobox inexistant... Je ne savais pas que tu espérais déclencher un SelectionChange sur un combobox. si j'avais su cela, nous aurions gagné du temps.

Normalement, tes combobox sont des activex, et donc tu pourrais gérer les évènements de ces contrôles. Dans le module de la feuille, combobox de gauche du panneau de code, tu as General et WorkSheet. Tu devrais aussi avoir Combobox1. En choissant Combobox1, tu verras les évènements que tu peux choisir à droite.

[Excel'air]

J'avais décidé de garder a en variable global car j'en avait besoin dans chacunes de mes procédures de contrôle.
J'avais essayé en passant par une range mais apparemment en utilisant un tableau j'ai la possibilité d'avoir une liste dynamique (où je pourrais rajouter des éléments dans ma colonne).

Tableau3 correspond à une plage d'une seule colonne et de 116 lignes pour l'instant.

Pour ta solution, on en revient à mon problème de départ c'est exactement ce que j'avais fais et tout fonctionnait bien, le problème était que je devais intégrer les événements sur chaque module de chaque feuille, or mes feuilles ne sont pas figés.

J'avais besoin d'une méthode pour automatiser ça, car mes feuilles peuvent être supprimés et re-créé.
A la base je cherchais une méthode pour pouvoir exporter des procédures de mon Module 1 vers mes feuilles.
D'ailleurs j'ai déjà essayé avec la méthode InsertLine, mais mon code est trop long.

Désolé je n'ai pas été très clair en exposant mon souci.

[Pierre Fauconnier]

J'ai probablement mal compris aussi.

je te propose la solution suivante, qui va utiliser une collection globale. C'est un peu touchy en ce sens que si ton code s'arrête brutalement, la collection est vidée. On devrait donc accompagner cela d'une gestion d'erreur.

La technique consiste à créer un objet personnalisé qui va gérer les évènements du combobox qu'on lui attribue. Et on va ajouter un objet personnalisé dans une collection publique par combobox présent sur une feuille.

Créons d'abord la classe perso pour gérer les évènements des combo. Je l'ai appelée SuperCombo. Il faut donc ajouter un module de classe et lui attribuer ce nom

Voici le code assez simple de cette classe, qui déclare un objet Combobox privé de manière à en gérer les évènements. Pour l'illustration, j'ai choisi ici de gérer le DropButtonClick qui appelle une procédure publique ProcCombobox en lui passant le contrôle en argument.

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
Option Explicit
 
Dim WithEvents mcbo As msforms.ComboBox
 
Property Let cbo(Value As msforms.ComboBox)
  Set mcbo = Value
End Property
 
Property Get cbo() As msforms.ComboBox
  Set cbo = mcbo
End Property
 
Private Sub mcbo_DropButtonClick()
  ProcFromCombobox mcbo
End Sub

On va charger la collection en balayant les feuilles du classeur, et sur chaque feuille, on va balayer les OLEObjects et, si l'OLEObject est de type Combobox, on l'ajoute à la collection grâce à une procédure dans un module standard

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
Sub AddComboBoxes()
  Dim o As OLEObject
  Dim sh As Worksheet
  Dim sc As SuperCombo
  Set colCBO = New Collection
 
  For Each sh In Worksheets
    For Each o In sh.OLEObjects
      If o.progID = "Forms.ComboBox.1" Then
        Set sc = New SuperCombo
        sc.cbo = o.Object
        colCBO.Add sc
      End If
    Next o
  Next sh
End Sub

Dans un module standard, on va placer la procédure appelée sur l'évènement du combobox

Code :

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

1
2
3
Sub ProcFromCombobox(cbo As msforms.ComboBox)
  Debug.Print cbo.Value
End Sub

Après avoir lancé la procédure AddComboBoxes, un clic sur le bouton d'ouverture de la liste de n'importe lequel des combobox présents sur les feuilles appellera la procédure ProcFromCombobox


J'espère avoir mieux cerné ton problème et y avoir mieux répondu

[Excel'air]

J'ai suivi tes instructions, j'ai donc un projet qui ressemble à ça :


Niveau optimisation ton code est super, on ajoute toutes les ComboBox1 à la collection colCBO, ensuite on attribue une classe de contrôle mcbo_DropButtonClick() qui va pouvoir interagir avec toutes les Combobox en même temps.

J'ai néanmoins un petit souci de compréhension, comment la classe de contrôle est elle censé récupérer ma collection ?
J'ai testé le code avec des MsgBox pour voir où il s’arrêtait, il n'y a que la classe de contrôle qui n'est pas détecté.
C'est dans ProcFromCombobox que je vais pouvoir appelé mes procédures de contrôle sur mes ComboBox ?

En tout cas merci beaucoup pour le temps consacré et pour ta patience

[Pierre Fauconnier]

Le process ne se passe pas exactement comme tu le décris.

On a un supercombo qui encapsule un combobox et qui gère ses évènements. Dans mon code, je n'ai mis qu'un évènement mais tu peux en choisir d'autre. Cet évènement ne fait rien d'autre que d'appeler une procédure standard et lui passe le combo sur lequel l'évènement s'est déclenché.

C'est ce supercombo qui est ajouté à une collection globale, de manière à être"active" durant toute la vie du projet en mémoire ( => un arrêt du code suite à une erreur, par exemple, "tue" le process et donc la collection).

La procédure standard est appelée exactement comme si tu avais un évènement sur un combobox géré par la feuille (comme tu l'avais essayé au début de tes essais). En fait, c'est comme si tu mettais l'évènement suivant pour chaque combo sur chaque feuille avec ceci comme code

Code :

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

1
2
3
Private Sub ComboBox1_DropButtonClick()
  ProcFromCombobox mcbo
End Sub

Sauf que la proc qui encapsule le combo dans un supercombo et qui charge celui-ci dans une collection le fait pour fois de façon dynamique. Ce moyen est également très intéressant pour déporter un évènement hors du conteneur de l'objet sur lequel survient l'évènement.

J'espère que c'est plus clair

[Excel'air]

Bonjour Pierre,

Ton explication était très clair, merci.

Mon code compile sans erreur, mais mon événement mcbo_DropButtonClick() ne fonctionne pas, alors que la procédure AddComboBoxes tourne parfaitement.
J'ai ajouté la déclaration suivante avec l'espoir que le contrôle fonctionne, mais rien..

Code :

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

Dim colCBO As Collection

J'ai fais quelques recherches dans l'aide VBA où j'ai appris qu'une collection était indexés, je n'ai pas trouvé grand chose sur les contrôles de collections (mais plutôt sur les collections de contrôles).
Y'a-t-il un moyen de vérifier que mes superCombo ont bien été ajoutés à ma collection et voir si cette collection est toujours active.
J'ai essayé avec ça, mais j'ai une erreur de compilation (Propriété ou méthode non géré par cette objet).

Code :

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

Print colCBO.Item(1)

Je ne vois vraiment pas d'où le problème peut venir

[Pierre Fauconnier]

[...]je n'ai pas trouvé grand chose sur les contrôles de collections (mais plutôt sur les collections de contrôles). [...]

Tu ne saurais pas trouver grand chose sur les contrôles de collection, car ça n'existe pas...

Une collection, ce n'est qu'un sac dans lequel tu mets des choses (souvent de même nature). Dès lors, à part ajouter, récupérer, supprimer, compter les choses que tu as mises dans le sac, tu ne sais pas faire grand-chose avec

"Toute la technique" repose ici sur le fait que les évènements d'un objet (qui en expose, évidemment), ne peuvent être écoutés que par des objets (dans d'autres langages, on parle de listener) . C'est pourquoi on passe pas un objet perso qui écoute les évènements du combobox qu'on lui attribue. Et on passe par un objet perso car on peut en créer autant qu'on veut et, par exemple, les stocker dans une collection globale qui permet qu'ils "existent" (= soient en mémoire) le temps que dure la collection. Et lorsque le listener (= le supercombo) entend du bruit sur l'objet qu'il contient et qu'on a prévu qu'il "gère" ce bruit, il le gère, par exemple, comme ici, en redirigeant l'exécution du code vers la procédure qui va bien... . Tu pourrais donc demander au supercombo d'écouter tous les évènements du combobox si tu le souhaitais..

Cette technique permet de délocaliser l'écoute des évènements d'un objet qui, par défaut, ne s'effectue que sur le conteneur de l'objet. En l'encapsulant dans un autre objet, on sépare l'écoute des évènements du conteneur initial. Dans le cas présent, le conteneur initial est la feuille sur laquelle le combobox est placé, et on déroute l'écoute en encapsulant l'objet dans un objet écouteur (le supercombo) qui redirige le code vers une procédure standard lorsque l'évènement est levé.