Voir le flux RSS

pijaku

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

Noter ce billet
par , 14/09/2018 à 15h42 (21 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

    1. 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.

    2. 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 : 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 : 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 : 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 : 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 : 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
    1. 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 : 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.

    2. 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 : 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.

    3. 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 : 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 : 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 : 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.


    4. Code global à insérer dans le Module ThisWorkbook :
      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
      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 :

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

    2. 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 : 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 : 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

Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Viadeo Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Twitter Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Google Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Facebook Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Digg Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Delicious Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog MySpace Envoyer le billet « Accéder aux événements des contrôles insérés dans une Frame placée dans une feuille de calcul » dans le blog Yahoo

Catégories
Sans catégorie

Commentaires