Ajout dynamique de contrôlesRésolu

Question

Bonjour le Forum,

Je rencontre une petite difficulté dans la création de mon code vba.

Voici le problème :

Par code, je créé une nouvelle feuille Excel, ensuite je créé un bouton d'action que je place sur cette feuille.
Jusque la tout vas bien.

Je ne parviens pas à associer une macro quand je clique sur le bouton de contrôle.

La procédure événementielle "Private Sub CommandButton1_Click()" fonctionne si elle est placée sur la feuille associée. Elle ne fonctionne plus si on place le code dans "ThisWorbook" ni dans un module.
Hors je ne peux pas placer le code dans la bonne feuille car celle ci est dynamique !

Elle n'existe pas initialement, donc je ne peux placer le code dessus.

Je suis donc coincé actuellement.

Avant j'utilisais un contrôle de formulaire et je pouvais mettre ".OnAction =    "MACRO""

Maintenant que j'utilise un contrôle ActiveX, je ne peux plus utiliser ".OnAction"

Quelles solutions me reste t'il ?

   Voici le code que j'utilise actuellement :   

Dim toto As OLEObject 'Déclaration des variables

'   =================================================================
'Création, insertion, placement et dimensionnement d'un object sur la feuille (Contrôle image dans cet exemple)
'Set toto ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=375, Top:=669.6, Width:=270, Height:=118.8)

'=================================================================
'On contrôle les propriétés
'toto.Name "CommandButton1"
toto.Left = Range("H39").Left
toto.Top = Range("H39").Top
toto.Width = Range("H39:K41").Width
toto.Height = Range("H39:K41").Height
toto.Object.Caption = "BLA BLA"
toto.Object.Font.Name = "Cambria"
toto.Object.Font.Size = 12
toto.Object.Font.Bold = True

toto.OnAction = "multiAbscisses" 'C'est ici que je bloque. Quel est l'équivalent pour un contrôle ActiveX ???


Merci à vous.

Cordialement,

André

SERIEUXETCOOL · Accepted Answer

Et me revoila une fois de plus.

Je pense avoir résolu mon problème. Finalement le lien que j'ai référencé 2 posts au dessus est un pure bijou. Il traite exactement la problématique de ce post.

Il peut paraitre un peu complexe au premier abord, mais en prenant un peu le temps d'analyser le code on se rend compte que c'est abordable.

Maintenant que j'ai saisi le truc, c'est un jeu d'enfant. Pas très intuitif comme méthode mais ça fonctionne.

Je place donc ci-après le code qui permet de créer une nouvelle feuille, de placer un nouveau bouton de commande (contrôle ActiveX), et de définir la procédure événementielle associée au clic sur ce bouton. (Code à placer dans un module standard)

Option Explicit

Public Sub test()

Dim Ws As Worksheet
Dim Obj As OLEObject
Dim laMacro As String
Dim x As Integer

Set Ws = Sheets.Add 'Ajoute une nouvelle feuille
    
'ajoute un CommandButton dans la nouvelle feuille
Set Obj = Ws.OLEObjects.Add("Forms.CommandButton.1")
With Obj
    .Name = "monBouton" 'renomme le bouton
    .Left = 50 'position horizontale par rapport au bord gauche de la feuille
    .Top = 50 'position verticale par rapport au bord haut de la feuille
    .Width = 150 'largeur
    .Height = 30 'hauteur
    .Object.Caption = "Supprimer données feuille"
End With

'Spécifie le contenu de la macro qui sera associée au bouton
laMacro = "Sub monBouton_Click()" & vbCrLf
laMacro = laMacro & "multiabscisse" & vbCrLf
laMacro = laMacro & "End Sub"

'Ajoute la procédure dans la feuille
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
    x = .CountOfLines + 1
    .InsertLines x, laMacro
End With

End Sub

Public Sub multiabscisse()
MsgBox "La macro multiabscisse est en cours. Nikel !"
End Sub


Il ne me reste plus qu'a tester sur mon code final mais normalement ça ne devrait pas poser le moindre soucis. Avec cette méthode, dès lors que je clique sur le bouton de commande, je re-dirigge vers la macro de mon choix et quelle que soit la complexité de cette macro.

Je reviens vers l'originalité de ce post, si l'on utilise un bouton de commande (contrôle de formulaire) la macro "multiabscisse" serait lancée via ".OnAction = "multiabscisse".
L'équivalent en utilisant un bouton de commande (contrôle ActiveX) est le code que je propose ci-dessus. C'est un peu plus long et moins intuitif mais le résultat est le même.

Voila donc la réponse que j'espérais obtenir.

Merci à ceux qui ont proposés leur aide. C'est gentil. Pour une fois que je trouve un truc bien^^. En même temps je n'ai rien inventé pour le coup.

Je remet l&#8217;excellent lien qui m'a permis d'avancer : Tapez le texte de l'url ici.

Bien cordialement,

André

Utilisateur anonyme · Answer

Bonjour,

[i]Hors je ne peux pas placer le code dans la bonne feuille car celle ci est dynamique !
 
Elle n'existe pas initialement, donc je ne peux placer le code dessus. /i

J'avoue que là j'ai de la misère à suivre. En principe, au moment de la création de la feuille, on peut lui donner un nom. Ensuite, on peut l'utiliser avec quelque chose du genre:

sheets("nom de la feuille").etc

Pour insérer des modules, ou du code, dans un classeur ou une feuille avec VBA, il y a des exemples chez Frédéric Sigonneau

ucfoutu · Answer

Bonjour, André,

Ce que tu fais est tortueux. Ne joue pas trop avec les ajouts dynamiques.

Voilà malgré tout, mais j'insiste : seulement pour l'exemple et pas pour approuver :

Dans le module des macros :
Sub faire_bouton()
  Dim toto As OLEObject
  Dim F As Worksheet
  Set F = Sheets("Feuil1")
  Set toto = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False)
    With toto
       .Name = "coucou"
       .Left = F.Range("H39").Left
       .Top = F.Range("H39").Top
       .Width = F.Range("H39:K41").Width
       .Height = F.Range("H39:K41").Height
       .Object.Caption = "BLA BLA"
       .Object.Font.Name = "Cambria"
       .Object.Font.Size = 12
       .Object.Font.Bold = True
    End With
End Sub

ce qui est au dessus crée un bouton sur Feuil1
Il te faut donc mettre le code de son écènement dans le module de code de Feuil1.
Exemple :
Private Sub coucou_Click()
  MsgBox "click constaté sur le nouveau bouton"
End Sub
Attention : assez instable ! Si ru rates ton coup ===>> tu auras intérêt à choisir un autre nom que coucou (tant dans la macro que pour l'évènement click) pour ton essai suivant. Il arrive en effet sinon que VBA s'y perde un peu et garde en mémoire certaines choses.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

ucfoutu · Answer

Si maintenant, tu veux, à la fois :
- Créer la feuille dynamiquement
- y insérer le code (évènement click montré plus haut) pour le bouton également créé dynamiquement, c'est possible, mais encore plus scabreux !
Je ne saurais trop te recommander d'éviter de telles démarches, grandes causes d'instabilité et jamais véritablement nécessaires.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

SERIEUXETCOOL · Answer

Re-Bonsoir,

cmarcotte : J'avoue que là j'ai de la misère à suivre. En principe, au moment de la création de la feuille, on peut lui donner un nom. Ensuite, on peut l'utiliser avec quelque chose du genre:

En effet après avoir nouvellement créé ma feuille, je lui attribue un nom et je me sert de son nom pour réaliser mes opérations (réalisé dans un module et jamais dans un objet feuille). Malheureusement, à chaque fois que le code est lancé je repars de 0. C'est à dire que je recrée ma feuille qui est donc toute fraiche dans l'arbre de construction du projet vba. Tout code qui s'y trouve, est automatiquement supprimé quand je relance la macro.
C'est pourquoi j'utilise "ThisWorbook" et jamais le nom d'une feuille.



Ucfoutu : Il te faut donc mettre le code de son écènement dans le module de code de Feuil1

Après avoir lancé une fois le code, la feuille1 existe bien dans arborescence. Si je met le code dans la feuille1 ça fonctionne. Mais, dès que je relance le code, comme je supprime la feuille pour la recréer ensuite et bien le code qui était dans la feuille1 n'y est plus. Et si je clique sur le bouton de commande, rien ne se passe.

C'est justement bien la tout mon problème^^


Pourtant cela ne me semble pas insurmontable quand regarde de loin. En gros je veux créer une feuille par code vba (facile), ensuite insérer un bouton de commande ActiveX par code encore (facile). Ne me reste plus qu'a associer une macro quand on clique sur le bouton, par code encore une fois (la je coince).

Le coup d'après la feuille est supprimée, donc le code qu'elle contient également. Jusque la, je me suis bien débrouiller pour tout mettre dans "ThisWorbook" ou dans un module sans utiliser les feuilles. Mais la je coince, car je ne sais pas comment lier l'évènement "clik" avec une macro existante sans passer par les "feuilles"



Je ne sais pas si je suis bien clair.

N'hésitez pas à me reprendre . Peut être que la méthode que j'emploie n'est pas la bonne. Dans ce cas la, comment parvenir au même résultat avec une autre démarche ?

Cordialement,
André

SERIEUXETCOOL · Answer

Je propose un truc bête mais sait on jamais...

Pour m'affranchir d'utiliser les modules liés aux feuilles j'utilise le module "ThisWorbook" qui est l'élément toujours présent dans le classeur.

Peut on utiliser un évènement similaire du type
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
avec Sh le nom de ma feuille créée dynamiquement et Target comme étant le bouton de contrôle sur lequel je clique ?

C'est comme ça que j'opère pour les cellules. Mais pour un bouton de contrôle je ne sais pas si c'est possible.

Dans tous les cas, j'ai des solutions fonctionnelles. Je ne suis pas coincé. Par exemple je peux utiliser un contrôle de formulaire plutôt qu'un contrôle activeX. Dans ce cas je peux utiliser "OnAction" sans passer par les feuilles.
Je peux aussi décider qu'au lieu de cliquer sur un bouton de commande, je clique sur une cellule ayant la même fonction. Dans ce cas j'utiliserai l'évènement Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) qui me détectera quand on cliquera sur la cellule. La encore je ne passe pas par le module de la feuille.

Évidement je souhaite réaliser la même chose avec un contrôle ActiveX. C'est l'objet de ce post.

Je suis convaincu que l'on peut bricoler une astuce simple pour "tromper" Excel.

André

ucfoutu · Answer

Plus je lis, plus je sens sinon carrément l'usine à gaz, tout au moins un défaut de conception.
Commençons donc par la moelle :
- si nous avons bien compris, tu veux doter chaque nouvelle feuille créée d'un bouton de commande utilisable sur cette feuille.
Question toute bête : quel code (instructions) devra s'exécuter au clic de ce bouton ?


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

SERIEUXETCOOL · Answer

Ucfoutu : Plus je lis, plus je sens sinon carrément l'usine à gaz

Je ne suis pas tellement d'accord. La méthode que j'emploie pour coder est très certainement une usine à gaz. Mais il n'en reste pas moins que la demande qui est faite sur ce post n'a rien avoir avec une usine à gaz.

C'est un besoin/problème comme un autre. Je cherche simplement à coder l'intégralité par code. Le livrable doit être uniquement un code. Pas d'ajouts de contrôles manuels sur une feuille, de mise en page ou quoi que ce soit. C'est ma contrainte principale. Tout doit être fait par code uniquement.
Donc si j'ai besoin à un moment donné d'un bouton de contrôle, et ben pas de soucis je le créé par le code. Si j'ai besoin de l'orienter, de le positionner, de modifier ses propriétés je le fait par code uniquement. Le code est justement fait pour ça. Donc si j'ai besoin d'associer une macro quand je clique sur un bouton de contrôle et ben ça doit pouvoir se faire par code comme tout le reste finalement.

Il ne s'agit la que d'un problème de codage pour moi. Si le langage n'est pas assez simplement adapté pour répondre à mon besoin c'est une chose. Mais je ne pense pas que ce soit une usine à gaz. Bien évidement c'est mon avis, rien de plus 


si nous avons bien compris, tu veux doter chaque nouvelle feuille créée d'un bouton de commande utilisable sur cette feuille. 

Pas sur chaque feuille obligatoirement. Sur mes 10 feuilles créés, une feuille contient 1 bouton de commande, et une autre feuille contient 2 boutons de commandes. Soit 3 boutons de commande sur 2 feuilles. Mais ça peut évoluer à tout moment. L'idée serait de savoir comment faire pour un seul bouton de commande. Le reste se généralise.

Je précise bien qu'à chaque fois que je lance le code, toutes les feuilles sont nouvellement créés (supprimées puis recrées de A à Z). Il n'est donc pas envisageable de s'appuyer sur les codes de modules liés aux feuilles.


Question toute bête : quel code (instructions) devra s'exécuter au clic de ce bouton ? 

En théorie, ça doit fonctionner pour tout. Donc du simple affichage d'un message en passant par une séries d'instructions plus poussées.

Dans mon cas actuel, l'action sur le premier bouton de commande permet d'afficher un Formulaire contenant une série de données à remplir. L'affichage se fait en cliquant sur ce bouton.
Un autre permet d'afficher ou de masquer des séries de données sur un graphique. Et enfin un autre permet de charger des images, de les modifier et de réaliser des petits traitements dessus.
Mes besoins sur ces boutons d'actions sont complètement indépendants. les instructions elles aussi.

Finalement je cherche simplement l'équivalent de "OnAction" qui fonctionne pour les contrôles de formulaires mais pour des contrôles ActiveX avec la contrainte supplémentaire de ne pas utiliser les codes de modules liées au feuilles.

J'ai vu des choses sur le net avec la même problématique. Mais je ne parviens pas encore à comprendre. C'est du bricolage, mais ça a le mérite d'exister.

Je pense qu'une bonne solution serait d'utiliser "ThisWorbook" pour détecter quand on clique sur un objet. Mais la je trouve rien...


Cordialement,

André

SERIEUXETCOOL · Answer

Bon je viens de me faire surprendre par le changement d'heure 
Il était 2 heures du mat et puis pouf, finalement il est 3 heures.
J'ai les boules. Du coup c'est l'heure d'y aller pour moi.

Néanmoins avant de partir je voulais faire le bilan de mes recherches sur le net. Il apparait comme évident que le problème que je soulève a déjà été traité par le passé. Des solutions existent donc. C'est rassurant.

D'autres part, j'ai pu correctement converger vers une méthode qui semble constamment porter ses fruits : Le contrôle dynamique des objets. Je créé ma feuille, place mon contrôle activex, et créé une procédure événementielle lié à ce bouton. Sur le papier ça semble bien, faut que je teste demain 

J'ai enfin pu mettre la main sur un tuto "simple" et clair. Je met le lien ci-après pour ceux que ça intéresse.

Du coup je le lierai correctement demain. Ucfoutu peut tu me donner ton avis sur cette méthode ? Enfin si tu as le temps d'y jeter un œil.

André


PS : Le lien du tuto

http://silkyroad.developpez.com/VBA/VisualBasicEditor/#LV-A

ucfoutu · Answer

Ce qu'expose le lien que tu as montré n'a rien de nouveau.
Et c'était d'ailleurs dans cette optique que je te demandais 
Question toute bête : quel code (instructions) devra s'exécuter au clic de ce bouton ? 

Car :
1) - une chose est de faire toujours exécuter le même code
2) - une autre est de déterminer un code différent pour chaque bouton
Et si on est dans le cas 1 : existent des solutions plus orthodoxes
Et si on est dans le cas 2 : cela voudrait dire quoi ? Que l'utilisateur devra choisir le code à écrire ? Le choisir où (parmi quoi ?), comment, et avec quelles notions informatiques ?
____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

SERIEUXETCOOL · Answer

Bonjour tout le monde,

Je suis content car je converge doucement vers la même solution que tu avais en tête Ucfoutu. Évidement pour moi c'est tout nouveau . Je n'avais même jamais entendu parler de module de classe pour être franc.

On se trouve dans le cas numéro 2 que tu expose Ucfoutu. Chaque bouton sera associé à une macro unique. En gros, chaque bouton de commande réalise une fonction unique quand on clique dessus.
Mais l'avantage, c'est que toutes les macros qui seront associées aux divers boutons d'action se trouvent listées les unes après les autres dans un seul et unique module standard. Je ne sais pas si c'est utile.


André

ucfoutu · Answer

Je vais te répondre très franchement, André :
1) Il n'est pas utile de créer un module de classe pour faire cela
2) comme je te l'ai déjà dit : les démarches de l'espèce sont source d'instabilité et nécessitent des dépendances elles-même instables.

3) pour tout dire : pour faire ce que tu exprimes là :
--- on utilise habituellement les commandbars (et on évite ainsi d'emplâtrer la feuille de calculs)
--- pire : on peut carrément tout éviter, bouton sur commandbars ou sur feuille ! Une simple instruction sur selection_change et target suffirait

Pour résumer : tout, n'importe quoi (et les solutions moins hasardeuses sont loin de manquer) sauf de tels ajouts dynamiques de bouton et de code associé.
Cela reste pour moi, fondamentalement, un aspect de conception.

Tu veux savoir comment sans module de classe ? ===>> en voici donc un exemple (double, en plus) que je viens de bâtir pour toi ===>>
Dim VBComp As VBComponent
    
    
    Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(1)
    VBComp.Name = "NouveauModule"
    
    ReDim tabl(3)
    tabl(0) = "Sub laMacro()"
    tabl(1) "  Range(""A1"").Value ""Coucou"""
    tabl(2) = "End Sub"
    
    
    With VBComp.CodeModule
      For i = 0 To UBound(tabl)
        .InsertLines .CountOfLines + 1 + i, tabl(i)
      Next
   
    
    ReDim Preserve tabl(4)
    tabl(0) = "Sub autreMacro()"
    tabl(1) "  Range(""A1"").Value ""blabla"""
     tabl(2) "  Range(""A1"").Value ""blibli"""
    tabl(3) = "End Sub"
    For i = 0 To UBound(tabl)
        .InsertLines .CountOfLines + 1 + i, tabl(i)
    Next
    End With
End Sub

Si tu en veux d'autres, pas de problème, y compris pour ajouter une sub d'évènement click dans le module de code où se trouve le bouton !
Tout dela commence par une première dépendance, déjà :
La référence à 
Microsoft Visual Basic for Applications Extensibility 5.3
et c'est là la première cause, déjà, d'une instabilité.
si tu veux jouer sur ce terrain-là, André, je n'y viendrai pas avec toi car s'éloigne trop de la prudence.
____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

ucfoutu · Answer

5Riens !
Je te parlais d'instabilité ?
Je viens à l'instant, après quelque manipulations de ce genre, de parveir à ce magnifique message d'erreur :
"Erreur d'exécution 1004 :
l'accès par programme au projet Visual Basic n'est pas fiable."
La ligne dénoncée utilise simplement
 Application.VBE
qui n'est d'un seul coup même plus reconnu, ni dans mon projet actuel, ni dans tout nouveau projet ouvert ! ===>>> il va probablement me falloir réinstaller Excel 2007 ! 



____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

ucfoutu · Answer

Hé bé !
Réparé, mais de cette manière :
Tapez le texte de l'url ici.
Je disais "instabilité" ? Qui, sinon mon propre programme, avait donc modifié "spontanément" les paramètres de sécurité ? Pas moi, en tout cas ! Ni les instructions simples de mon programme !
Alors, André : ===>> A toi de voir si tu veux un jour, de manière totalement inattendue et non justifiée, devoir chercher la cause soudaine d'un tel comportement . 


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

SERIEUXETCOOL · Answer

Salut Ucfoutu,

Merci pour le complément d'infos. Je prends note d'éventuelles futures complications dans mon projet.

Toute fois, je dois avouer être séduit par la méthode que je propose quelques posts ci-dessus.
Je viens de finir de l'appliquer proprement à mon code. Je n'ai rencontré aucun souci pour le moment.
Le principe me parais presque logique maintenant.

Et en utilisant cette méthode je suis enfin parvenu à réaliser proprement ce que je voulais faire depuis le début. Je peux enfin tout gérer par une méthode assez simple.

Donc à priori je prends le risque pour le moment. Je verrai avec le temps si je suis confronté à une instabilité. j'ai des doutes tout de même car finalement dans le code la seule partie qui peut poser problème est la suivante :
'Ajoute la procédure dans la feuille
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
    x = .CountOfLines + 1
    .InsertLines x, laMacro
End With

Et ce n'est pas franchement méchant comme opération je trouve.

Mais à confirmer dans le temps. En plus je n'utilise plus de module de classe, ni même la référence à Microsoft Visual Basic for Applications Extensibility 5.3.

Pour le moment après plus d'une 20 aine de lancement, je n'ai pas eu le moindre problème.

Je reste ainsi pour l'instant. Ça me permet au moins de continuer à avancer le reste du code.

En tout cas Ucfoutu, merci de ta bienveillance. J'apprécie sincèrement.


Bien cordialement,

André

ucfoutu · Answer

Il s'agit de ton appli. Tu fais donc comme tu l'entends.
Je suis quant à moi (mais c'est surtout pour m'entraîner) en train de voir comment ne créer qu'un seul bouton de chaque type et l'utiliser, ainsi que son code, sur toutes les feuilles que l'on veut, en l'affichant là où l'on veut, sans donc le moindre rajout dynamique (ni de code, ni de bouton). 

Intérêt évident : moins de code - disparition du risque d'instabilité - un seul bouton avec son code.


____________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches d'autres forumeurs.
Pas d'aide en ligne installée ? ==> ne comptez pas sur moi pour simplement vous dire ce qu'elle contient

SERIEUXETCOOL · Answer

Tjs à la recherche de défis Ucfoutu 

J'en encore plein de casses têtes si besoin est^^ Mais je préfère ne dévoiler que ceux ou je bloque vraiment.

Actuellement je me prends la tête avec le fait qu'Excel 2007 n'enregistre quasiment rien sur les graphiques.

C'est rageant je trouve. Je vais attendre demain de mettre la main sur un Excel 2003 hé hé.

Ton idée est intéressante Ucfoutu. Bon courage 

A bientôt, et merci.

André

Ajout dynamique de contrôles

17 réponses

Votre réponse

Discussions similaires