pijaku

Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul

0 Commentaires

par

pijaku

, 14/09/2018 à 15h42 (495 Affichages)

________________________________________________________________

Introduction

L'intérêt de cette contribution est multiple.
- La méthode d'insertion de contrôles dans une Frame d'une feuille de calcul semble assez peu connue,
- Elle permet de créer des boîtes de dialogue personnalisées (MsgBox, InputBox particulières, Calendrier, Calculatrice, etc...) se substituant donc aux Userforms.
Mais alors, pourquoi vouloir substituer une Frame à l'Userform?
> question de positionnement sur la feuille de calcul,
> alléger le code d'au moins un module (celui de l'Userform).

I- En mode création

Insertion de contrôles
La méthode est très simple.
1. Dessinez une Frame sur votre feuille :
  - Onglet développeur ==> cliquez sur le menu "Insérer"
  - Sous "contrôles Activex", cliquez sur "Autres contrôles" (icône composée d'un marteau croisé à une clé)
  - Recherchez et sélectionnez le contrôle : Microsoft Forms 2.0 Frame
  - Cliquez sur OK, puis dessinez votre Frame.
2. Dans le Frame, effectuez un clic droit
3. Sélectionnez : Objet cadre>Edition
La boîte à outils Contrôles s'affiche alors.
Vous pouvez maintenant dessiner tout type de contrôle dans votre Frame.
Pour l'exemple, nous allons dessiner une ComboBox et un CommandButton.

Associer des événements
Une fois votre Frame remplie de ses contrôles, si l'on bascule sous l'éditeur VBA, on se rend compte que, dans le module de la feuille concernée, le menu "général" ne donne pas accès aux contrôles que nous venons de dessiner. En effet, ces deux contrôles (combobox et bouton de commande) sont inaccessibles par la feuille car dessinés dans un autre container, notre Frame.
Ces contrôles étant de type MSForms.XXX, nous pouvons déclarer des variables de ce type avec activation des événements.
En entête de Module, il nous suffit d'écrire :

Code vba :

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

1
2
3
4
Option Explicit
 
Private WithEvents Combo1 As MSForms.ComboBox
Private WithEvents Bouton1 As MSForms.CommandButton

Dans le menu déroulant "général", nous avons maintenant accès à Bouton1 et Combo1 ainsi qu'à leurs événements, dans le menu déroulant "Déclarations".
Ne reste plus qu'à affecter à nos deux variables, nos deux contrôles.
Pour cela, dans l'événement de votre choix, il suffit de les affecter comme ceci :

Code vba :

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

1
2
3
4
5
6
Private Sub Worksheet_Activate()
    With Frame1
        Set Combo1 = .Controls("Combobox1")
        Set Bouton1 = .Controls("CommandButton1")
    End With
End Sub

Ajoutons-y, pour pousser un peu plus avant les tests, une propriété List à notre ComboBox :

Code vba :

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

1
2
3
4
5
6
7
Private Sub Worksheet_Activate()
    With Frame1
        .Controls("Combobox1").List = Range("F1:F5").Value
        Set Combo1 = .Controls("Combobox1")
        Set Bouton1 = .Controls("CommandButton1")
    End With
End Sub

Ne reste plus qu'à créer vos propres procédures événementielles, par exemple :

Code vba :

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
Option Explicit
 
Private WithEvents Combo1 As MSForms.ComboBox
Private WithEvents Bouton1 As MSForms.CommandButton
 
Private Sub Bouton1_Click()
    MsgBox Combo1.Text
End Sub
 
Private Sub Combo1_Change()
    Range("A1") = Combo1.Text
End Sub
 
Private Sub Worksheet_Activate()
    With Frame1
        .Controls("Combobox1").List = Range("F1:F5").Value
        Set Combo1 = .Controls("Combobox1")
        Set Bouton1 = .Controls("CommandButton1")
    End With
End Sub

II- Dynamiquement (At run time)

Le but ici est de créer dynamiquement un Frame, d'y insérer deux contrôles (1 CommandButton et 1 ComboBox) et de faire en sorte que ces deux contrôles réagissent à leurs événements.
Remarque : Avant de commencer, il nous faut noter que l'événement déclencheur est déterminant. Si vous choisissez, par exemple, de créer votre Frame et ses contrôles par le clic sur un bouton ou par l'événement Worksheet_Activate, il vous faut savoir que cela nécessitera une autre procédure, secondaire (bouton en dehors du Frame ou Macro) pour affecter les variables "événementielles" à vos contrôles.

Nous choisirons donc, pour plus de simplicité, l'événement Workbook_Open().
Cet événement n'étant pas celui d'une feuille de calcul, il nous faudra déclarer quelques variables et constantes utiles en entête du module ThisWorkbook :

Code vba :

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

1
2
3
4
5
Private Wsh As Worksheet '==> la feuille concernée
Private Const strSheetName As String = "Feuil1" '==> le nom de la feuille concernée
Private Const strFrameName As String = "MyFramePerso" '==> le nom de la frame à insérer
Private Const strComboName As String = "MyComboPerso" '==> le nom de la combobox à créer
Private Const strButtName As String = "MyButtonPerso" '==> le nom du bouton à créer

Insérer une Frame sur une feuille de calcul :
Une Frame, dans une feuille de calcul, appartient à la collection OLEObjects.
Nous allons donc déclarer une variable de ce type. Cette variable va nous servir dans plusieurs procédures.
Si vous le souhaitez, vous pouvez donc la déclarer en entête de module. J'ai fait le choix de la passer en paramètre des procédures concernées.

Code vba :

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
Private Sub Workbook_Open()
Dim Frm As OLEObject
    Set Wsh = Worksheets(strSheetName)
    InsertFrame Frm, Range("F20"), 200, 100
End Sub
 
Private Sub InsertFrame(F As OLEObject, rngCell As Range, W As Single, H As Single)
'insère une Frame sur la cellule référencée par rngCell
    Set F = Wsh.OLEObjects.Add("Forms.Frame.1")
    With F
        .Name = strFrameName
        .Height = H
        .Width = W
        .Left = rngCell.Left
        .Top = rngCell.Top
    End With
End Sub

A noter : Le paramètre rngCell représente l'objet Range sur lequel nous allons positionner la Frame.

Création des contrôles dans la Frame :
Rien de bien compliqué. Il faut juste savoir que l'on peut ajouter, à la propriété Object des objets OLEObject, un (des) contrôle(s) de type MSForms.Control, grâce à sa méthode Add.

Code vba :

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
27
28
29
30
31
32
33
34
Private Sub Workbook_Open()
Dim Frm As OLEObject
    Set Wsh = Worksheets(strSheetName)
    InsertFrame Frm, Range("F20"), 200, 100
    InsertControlsInFrame Frm
End Sub
 
Private Sub InsertControlsInFrame(F As OLEObject)
    With F
        With .Object.Add("Forms.ComboBox.1")
            .Name = strComboName
            .Top = 15
            .Left = 30
            .Height = 20
            .Width = 75
            .Object.Font.Name = "Arial"
            .Object.Font.Size = 12
            .Object.AddItem "Ananas"
            .Object.AddItem "Pomme"
            .Object.AddItem "Poire"
        End With
        With .Object.Add("Forms.CommandButton.1")
            .Name = strButtName
            .Top = 45
            .Left = 30
            .Height = 20
            .Width = 75
            .Object.Font.Name = "Arial"
            .Object.Font.Size = 12
            .Object.Caption = "BOUTON3"
        End With
        .Verb
    End With
End Sub

Remarque : En fin de code, la méthode Verb est utilisée pour que l'utilisateur puisse atteindre les contrôles. Sans cette méthode, vous ne pourrez pas cliquer sur les contrôles de votre Frame.

Affecter les événements aux contrôles :
Ici, avec une ComboBox et un CommandButton, nous nous contenterons du Click().
Pour cela, il nous faut, tout comme en mode création, déclarer des variables "événementielles".
Notre entête de module devient donc :

Code vba :

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

1
2
3
4
5
6
7
8
9
10
Option Explicit
 
Private WithEvents Combo1 As MSForms.ComboBox
Private WithEvents Bouton1 As MSForms.CommandButton
 
Private Wsh As Worksheet
Private Const strSheetName As String = "Feuil1"
Private Const strFrameName As String = "MyFramePerso"
Private Const strComboName As String = "MyComboPerso"
Private Const strButtName As String = "MyButtonPerso"

Ensuite, créons nos deux procédures événementielles :

Code vba :

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

1
2
3
4
5
6
7
Private Sub Bouton1_Click()
    MsgBox Combo1.Text
End Sub
 
Private Sub Combo1_Click()
    Range("A1") = Combo1.Text
End Sub

Et enfin, la procédure d'affectation des contrôles à ces variables :

Code vba :

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

1
2
3
4
5
6
7
8
9
10
11
12
Private Sub Workbook_Open()
Dim Frm As OLEObject
    Set Wsh = Worksheets(strSheetName)
    InsertFrame Frm, Range("F20"), 200, 100
    InsertControlsInFrame Frm
    AffectVariables
End Sub
 
Private Sub AffectVariables()
    Set Combo1 = Wsh.OLEObjects(strFrameName).Object.Controls(strComboName)
    Set Bouton1 = Wsh.OLEObjects(strFrameName).Object.Controls(strButtName)
End Sub

On voit clairement dans cette procédure AffectVariables que les contrôles sont bien des membres de la propriété Object de l'objet OLEObject.

Code global à insérer dans le Module ThisWorkbook :

Code vba :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
Option Explicit
 
Private WithEvents Combo1 As MSForms.ComboBox
Private WithEvents Bouton1 As MSForms.CommandButton
 
Private Wsh As Worksheet
Private Const strSheetName As String = "Feuil1"
Private Const strFrameName As String = "MyFramePerso"
Private Const strComboName As String = "MyComboPerso"
Private Const strButtName As String = "MyButtonPerso"
 
Private Sub Bouton1_Click()
    MsgBox Combo1.Text
End Sub
 
Private Sub Combo1_Click()
    Range("A1") = Combo1.Text
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
    Wsh.Shapes.Range(Array(strFrameName)).Delete
End Sub
 
Private Sub Workbook_Open()
Dim Frm As OLEObject
    Set Wsh = Worksheets(strSheetName)
    InsertFrame Frm, Range("F20"), 200, 100
    InsertControlsInFrame Frm
    AffectVariables
End Sub
 
Private Sub InsertFrame(F As OLEObject, rngCell As Range, W As Single, H As Single)
    Set F = Wsh.OLEObjects.Add("Forms.Frame.1")
    With F
        .Name = strFrameName
        .Height = H
        .Width = W
        .Left = rngCell.Left
        .Top = rngCell.Top
    End With
End Sub
 
Private Sub InsertControlsInFrame(F As OLEObject)
    With F
        With .Object.Add("Forms.ComboBox.1")
            .Name = strComboName
            .Top = 15
            .Left = 30
            .Height = 20
            .Width = 75
            .Object.Font.Name = "Arial"
            .Object.Font.Size = 12
            .Object.AddItem "Ananas"
            .Object.AddItem "Pomme"
            .Object.AddItem "Poire"
        End With
        With .Object.Add("Forms.CommandButton.1")
            .Name = strButtName
            .Top = 45
            .Left = 30
            .Height = 20
            .Width = 75
            .Object.Font.Name = "Arial"
            .Object.Font.Size = 12
            .Object.Caption = "BOUTON3"
        End With
        .Verb
    End With
End Sub
 
Private Sub AffectVariables()
    Set Combo1 = Wsh.OLEObjects(strFrameName).Object.Controls(strComboName)
    Set Bouton1 = Wsh.OLEObjects(strFrameName).Object.Controls(strButtName)
End Sub

Conclusion :

Autres exemples :
Ces exemples correspondent aux divers tâtonnements qui m'ont amené à cette contribution.
Il sont disponibles dans la discussion d'origine.

Extension aux autres logiciels Office :
Il en va de même pour tous les logiciels de la suite Office.
La différence tiendra surtout dans les événements déclencheurs.
Voici deux petits exemples de code avec un Frame dessiné en mode création, dans lequel nous aurons placé (même méthode qu'au I-1.) un CommandButton.

Sous Powerpoint :
Ici l'événement "déclencheur" est le Frame1_GotFocus().

Code vba :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 
Private WithEvents Bouton1 As MSForms.CommandButton
 
Private Sub Bouton1_Click()
    MsgBox "YES !!!"
End Sub
 
Private Sub Frame1_GotFocus()
    With Frame1
        Set Bouton1 = .Controls("CommandButton1")
    End With
End Sub

Ce code est à placer dans le Module du Slide concerné.

Sous Word :
Onglet développeurs, dans la rubrique contrôles cliquez sur Outils hérités puis sur les Autres contrôles Activex.
Ici l'événement "déclencheur" est le Document_Open().

Code vba :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Option Explicit
 
Private WithEvents Bouton1 As MSForms.CommandButton
 
Private Sub Bouton1_Click()
    MsgBox "ça fonctionne!"
End Sub
 
Private Sub Document_Open()
    With Frame1
        Set Bouton1 = .Controls("CommandButton1")
    End With
End Sub

A noter sous Word, lors du clic droit dans le Frame, la présence, dans l'objet cadre d'un Item "Convertir". Cela ne change rien au sujet dont il est question ici.

Un grand merci à Jacques (unparia) pour la relecture.

A bientôt,
Franck

Mis à jour 10/11/2018 à 15h54 par LittleWhite (Coloration du code)

Tags: vba excel

Catégories: Sans catégorie

« Les différentes méthodes de détermination d'un Range "utile" Liste des billets Le jeu du Démineur »

pijaku

Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul

Commentaires

+ Créer un blog

Catégories de l'utilisateur

Archives

<	Août 2025
Di	Lu	Ma	Me	Je	Ve	Sa
27	28	29	30	31	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	27	28	29	30
31	1	2	3	4	5	6

<	Août 2025
Di	Lu	Ma	Me	Je	Ve	Sa
27	28	29	30	31	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	27	28	29	30
31	1	2	3	4	5	6

<	Août 2025
Di	Lu	Ma	Me	Je	Ve	Sa
27	28	29	30	31	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	27	28	29	30
31	1	2	3	4	5	6