créer un bouton dynamiquement excel via VBA

Question

Bonjour ,
je souhaite créer un bouton par macro et affecter une macro également à ce bouton créé, sachant que le code lié à la macro qui doit affecter doit pouvoir être changé dynamiquement en fonction du contenu de certaines cellules.
j'ai écrit le code suivant :
Dim X As Byte
Dim Code As String
Dim NextLine As String
Dim oOLE As OLEObject
Sheets("Menu").Select
Range("A1").Select
i = 1
Do While Cells(i, 1) <> "XXX"
i = i + 1
Loop
i = i + 1
BU_number = Cells(i, 1)

Workbooks.Add 'creer classeur
Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=340, Top:=30, Width:=100, Height:=30)
'Left position bouton par rapport au bord gauche de la feuille
'Top position bouton par rapport au haut de la feuille
'Width largeur bouton
'Height hauteur bouton

X = ActiveSheet.OLEObjects.Count 'compter le nombre de boutons existants dans la feuille

'option nommer l'objet
oOLE.Name = "CommandButton" & X
'texte sur le bouton
ActiveSheet.OLEObjects(X).Object.Caption = "BU " & X
Code = "Sub CommandButton" & X & "_Click()" & vbCrLf
Code = Code & "Sheets(""feuil2"").select" & vbCrLf
Code = Code & "End Sub"

With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
NextLine = .CountOfLines + 1
.insertlines NextLine, Code
End With
End Sub

ce code fonctionne très bien lorsqu'on crée une nouvelle feuille "Workbooks.Add 'creer classeur" après la boucle.
Le fait d'appuyer sur le bouton me fait aller sur la feuille "feuil2"

par contre dès qu'on supprime la ligne "Workbooks.Add 'creer classeur "
qui permet donc de créer le bouton sur la feuille active cane marche plus j'ai le message suivant :
"Impossible d'entrer en mode arrêt maintenant"
puis si je fais continuer : message : l'indice n'appartient à la sélection"

Quelqu'un peut m'aider sur le sujet
merci d'avance

Utilisateur anonyme · Accepted Answer

Bonjour,

J'ai apporté quelque modif pour que cela fonctionne chez moi !

Sub CreerMenu()

    Dim X As Byte
    Dim i, BU_number As Long
    Dim Code As String
    Dim NextLine As String
    Dim oOLE As OLEObject
    
    Sheets("Menu").Select
    Range("A1").Select
    i = 1
    Do While Cells(i, 1).Value <> "XXX"
        i = i + 1
    Loop
    i = i + 1
    BU_number = Cells(i, 1)

    'Workbooks.Add 'creer classeur
    Set oOLE = ActiveWorkbook.ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Link:=False, DisplayAsIcon:=False, Left:=340, Top:=30, Width:=100, Height:=30)

    'Left position bouton par rapport au bord gauche de la feuille
    'Top position bouton par rapport au haut de la feuille
    'Width largeur bouton
    'Height hauteur bouton

    X = ActiveSheet.OLEObjects.Count 'compter le nombre de boutons existants dans la feuille

    'option nommer l'objet
    oOLE.Name = "CommandButton" & X
    'texte sur le bouton
    ActiveSheet.OLEObjects(X).Object.Caption = "BU " & X
    Code = "Sub CommandButton" & X & "_Click()" & vbCrLf
    Code = Code & "Sheets(""feuil2"").select" & vbCrLf
    Code = Code & "End Sub"

    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
        NextLine = .CountOfLines + 1
        .insertlines NextLine, Code
    End With

End Sub

Lupin

Utilisateur anonyme · Answer

Bonjour,

vous devez ajouter cette syntaxe :


With myCommandButtonDetail
            .Name = "CommandButtonDetail_" & iNbReappro
            .OnAction = "'Nom de la routine a appeller'"
            '-- le reste des param
End With


attention, la ligne se lit :

.OnAction = "    'Nom de la routine a appeller'   "

Le nom de la routine doit être entre simple guillemets  ' ... '
et le tout doit être entre double guillemets  "  ' ... ' "

Lupin

Naya · Answer

Bonjour,

il me dit que la méthode ou propriété n'est pas gérée par cet objet

j'ai essayé en utilisant les OOleobject comme vous avez fait precedemment mais ca ne marche pas car je veux mettre l'objet sur un formulaire et ca ne marche pas :

quand je fais ca (pour tester) ca marche :
Set oOLECommandButtonDetail = ActiveWorkbook.ActiveSheet.OLEObjects.Add  ( arguments...)

mais quand je fais ca (ce que je voudrai) ca ne marche pas :
Set oOLECommandButtonDetail = GestionMeuleForm.MultiPage1.Pages(4). _
CalculBesoinFrame.OLEObjects.Add(arguments...)

merci pour votre réponse

Claire

Utilisateur anonyme · Answer

Bonjour,

Dans un premier temps, je dois vous avouer que j'ai confondu
un bouton menu au type de bouton que vous tentez d'installer.

J'ai testé votre code en le modelant pour m'en faire un exemple
et il semble que ce soit un bogue connue de microsoft d'après
les recherches que j'ai fais.

Désolé de ne pouvoir aller plus loin !

Lupin

Utilisateur anonyme · Answer

re:

voici le code que j'ai testé :


Sub TestCreationBouton()

    Dim myCommandButtonDetail  As CommandButton
    Dim iNbReappro As Integer, NextLine As Integer
    Dim strCode As String
    
    iNbReappro = 1
    
    Set myCommandButtonDetail = GestionMeuleForm.MultiPage1.Pages("Page4").CalculBesoinFrame.Controls.Add _
        ("Forms.CommandButton.1", "CommandButtonDetail_" & iNbReappro)
    
    With myCommandButtonDetail
        .Name = "CommandButtonDetail_" & iNbReappro
        .Caption = "Run"
        .Top = 50
        .Left = 100
    End With

    strCode = ""
    strCode = strCode & "Private Sub CommandButtonDetail_" & iNbReappro & "_Click()" & vbCrLf & vbCrLf
    strCode = strCode & "'    Load DetailReapproForm" & vbCrLf
    strCode = strCode & "'    GestionMeuleForm.Hide" & vbCrLf
    strCode = strCode & "'    DetailReapproForm.Show" & vbCrLf
    strCode = strCode & "    Msgbox" & """" & "Réussi" & vbCrLf & vbCrLf
    strCode = strCode & "End Sub" & Chr(13)
    
    With ThisWorkbook.VBProject.VBComponents("GestionMeuleForm").CodeModule
        NextLine = .CountOfLines + 1
        .InsertLines NextLine, strCode
    End With


End Sub
'

Lupin

Stim · Answer

Bonjour,

Dans mon cas je créer une feuille dynamiquement dans le code et je créer un bouton dans celle ci, mais quand je veux insérer du code a la ligne :
    With ThisWorkbook.VBProject.VBComponents(IntituleCre).CodeModule


Il me sort l'erreur :
"Erreur d'execution 9 :
L'indice n'appartient pas à la selection"

Et ceci peut importe ce que je met dans le with (meme quand le with est vide ...)

Aurais-je oublié qqchose ?

Utilisateur anonyme · Answer

Bonjour Stim,

With ThisWorkbook.VBProject.VBComponents(IntituleCre).CodeModule 

devrait se lire :

With ThisWorkbook.VBProject.VBComponents("IntituleCre").CodeModule 

à moins que  [ IntituleCre ] soit une variable et dans ce cas, c'est qu'elle
n'a pas été initialisé.

Lupin

Stim · Answer

Merci pour la précision lupin ;)

Mais le probleme n'est pas la en fait ^^ (IntituleCre est bien une variable qui contient le nom de la feuil)

Je viens de voir (en listant les vbcomponents) qu'en fait le component s'appelle "Feuil6" et cela marche très bien quand j'utilise ce nom la ... donc en fait, il me faudrait une méthode qui me renvoi le nom VB de cette feuille...

Utilisateur anonyme · Answer

re :

du genre :


Sub ChercheFeuille()
    
    Dim VBP As Variant
    Dim VBC As VBComponent
    Dim Message As String
    
    On Error Resume Next
    Set VBP = ActiveWorkbook.VBProject
    Message = ""
    With VBP
        For Each VBC In .VBComponents
            'Message = Message & vbLf & VBC.Name
            Select Case VBC.Type
                'Case 1: Message = Message & vbLf & "Module "
                'Case 2: Message = Message & vbLf & "Class Module "
                'Case 3: Message = Message & vbLf & "UserForm "
                Case 100: Message = Message & vbLf & "Document Module " & VBC.Name
            End Select
        Next VBC
    End With
    MsgBox Message
    
End Sub
'


Lupin

stim · Answer

En fait plus simplement j'ai trouvé la fonction "ActiveSheet.CodeName" qui me permet de récupérer le nom interne de la feuille, demandée par la fonction VBComponents...

Apres savoir pourquoi je peut pas mettre en direct le nom externe de la feuille ... la ca me dépasse ^^

Merci quand meme pour toutes tes réponses Lupin ;)

++

xaphie · Answer

Bonjour

Je ne suis pas super fort en VBA sur Excel, donc j'aurais besoin d'un petit coups de mains. 
je vous explique un peu la structure de mon fichier.

j'ai une feuille de config qui me permet de créer toutes les autres autres feuilles du classeur.
dans ces feuilles, j'ai une feuille qui sert de "base" où je veux créé des boutons dynamiquement suivant le nom donné dans ma feuille de config.
c'est bouton devrais me faire aller directement à la feuille choisit
ex: bouton nommé "groupe" m'envoie sur la feuille "groupe"
Mon problème c'est que je ne sais pas comment récupérer le nom du boutons.

voici le code que j'envisager

sub choix_feuille()
dim nom_feuille as string

nom_feuille = ???????????

if feuille_existe then
    sheets(nom_feuille).select
    range("A1").select
else
    msgbox ("Impossible, la feuille n'existe pas)
end if

Merci de votre aide

Utilisateur anonyme · Answer

Bonjour,

En supposant que la feuille de config se nomme [ Base ] !
En supposant que le bouton est placé sur la feuiile Base et qu'il se nomme [ Groupe ] !

Placer ce code derrière la feuille :

Option Explicit

Private Sub Groupe_Click()
    Call Module1.Choix_Feuille(Me.Groupe.Name)
End Sub


et placer ce code dans un module nommé [ Module1 ].

Option Explicit

Sub Choix_Feuille(Nom_Feuille As String)

    Nom_Feuille = Sheets("Base").Groupe.Name

    If (Feuille_Existe(Nom_Feuille)) Then
        Sheets(Nom_Feuille).Select
        Range("A1").Select
    Else
        MsgBox "Impossible, la feuille n'existe pas"
    End If

End Sub
'

Public Function Feuille_Existe(Feuille As String) As Boolean
    Dim wsFeuille As Worksheet
    Feuille_Existe = False
    For Each wsFeuille In Worksheets
        If (wsFeuille.Name = Feuille) Then
            Feuille_Existe = True
            Exit For
        End If
    Next wsFeuille
End Function
'

xaphie · Answer

Salut

Merci pour cette reponse

En fait ce que je voudrais, c'est de recupérer le nom du bouton qui ce nomme "groupe" dans la variable Nom_feuille de ma mcaro ci-dessous.

Sub Choix_Feuille(Nom_Feuille As String)

    Nom_Feuille = ??? nom_du_bouton_sur lequel je click ??????

    If (Feuille_Existe(Nom_Feuille)) Then
        Sheets(Nom_Feuille).Select
        Range("A1").Select
    Else
        MsgBox "Impossible, la feuille n'existe pas"
    End If

End Sub

Je voudrais faire comme ça, car j'ai une macro qui me crée mes boutons en fonction d'une base (la feuille de config ). donc chaque bouton porte un nom qui correspond à une feuille et tous les boutons pointent sur la macro "choix_feuille" ci dessus. C'est pour ça que je veux recuperer le nom du bouton pour que lors du click sur le bouton groupe1 je vais sur la feuille groupe1 si je clik sur groupe2 je vais sur la groupe2 et ainsi de suite.

Je n'ai pas très bien compris  comment fonctionne ta solution, mais je crois qu'il faut connaitre le nombre de boutons et créer une macro par bouton. Si j'ai bien compris, ta solution ne convient pas trop à ce que je voudrais faire car je ne connais pas le nombre de bouton qui sera créé car il sont créés à partir de la feuille de config et par conséquent je ne peu créer autant de macro que de bouton. d'ou la solution que voudrais mettre en place, mais je suis bloqué sur la manière de récupérer le nom du bouton sur lequel je click.
Deplus cela permet de fair des bouton dynamique.

Merci de ton aide si tu voie une autre maniere pour arriver à ce que je veux suis preuneur.

Utilisateur anonyme · Answer

re:

la il y a un problème !!!

Sous VBA il est impossible de créer un groupe de controle, donc chaque bouton
renvoie a une routine distincte !

ex:

Si le premier Bouton se nomme [ Groupe1 ]
Lorsque tu clique sur le bouton tu arrives
sur la procédure :

Private Sub Groupe1_Click()
End Sub

ainsi tu appelle ta procédure en passant le nom du bouton.

Private Sub Groupe1_Click()
      Call Module1.Choix_Feuille("Groupe1")
End Sub
'

Si tes boutons, s'insère en dynamique, tu devras aussi
insérer le code correspondant de façon dynamique !

Lupin

guiss · Answer

Bonjour,

je fais mes premiers pas seul sur Visual basic et j'essaie de comprendre une série d'exercices simples que je commenc à faire (avec un peu de difficultés...)

j'essaie donc de créer sur ma feuille excel des boutons via visaul basic dans le cadre de la macro.

De base, il s'agit d'un exercice sur uen fonction de calcul de cercle dans lequel j'ai évidemment encodé la valeur de pi, celle du rayon, ... mais je n'arrive pas à cerner comment faire apparaitre sur ma feuille les boutons que je tente de créer (style appel_u_calcul , appel_p_calcul) et comment faire apparaitre un bouton "bouton form appel usf".

Car je n'arrive pas à créer et donc à faire le calcul avec userform

Bref, des premiers pas difficiles ; merci de l'aide apporter à ce petit sms d'un terrien en détresse

Utilisateur anonyme · Answer

Bonjour,

Si je comprends bien vous tenter de créer un/des bouton(s) sur une feuille de facon dynamique.

Commencer déja par faire les manipulations une fois sous l'enregistreur de macro !
Menu Excel  // Outils / Macro / Nouvelle macro
Décomposé vos actions en plusieurs petites macros.

Exemple
Créer un bouton
Créer le code associé ( la fonction de calcul ) !
Attacher les morceaux
Epurer les macros
Créer la routine

Sous Excel
Alt F11

Faite un copier/coller du code généré par l'enregistreur et je vous guiderai pour le
nettoyage et l'ajout de boucle et condition.


A la base je puis vous conseiller d'utiliser le classeur que j'ai publié sur Excel-DownLoad
https://www.excel-downloads.com/resources/categories/utilitaires.18/
Rechercher VizioXla, vous aurez une modele de base pour presque tous les objets d'excel !

Lupin

chlore · Answer

Bonsoir,

Je rencontre le même genre de soucis que les personnes précédentes, je m'explique :
dans un onglet de mon classeur excel je crée dynamiquement des boutons associés à une macro qui me permettent de restituer dans un autre onglet des données filtrées sur le numéro de projet listé. Tous mes boutons mènent vers la même macro qui doit pouvoir identifier la ligne sur laquelle est placé le bouton cliqué, hors je ne trouve pas comment récupérer la ligne afin de savoir sur quoi filtrer ma restitution.
Merci d'avance de votre aide

Wackolas · Answer

Bonjour,

je n'ai trouvé que ce sujet correspondant à peu près à mon besoin, donc je pose ma question aussi.

J'ai une macro excel qui créé une vingtaine d'onglets et un onglet de récap, et j'ai utilisé vos conseils pour créer dans l'onglet de récap une liste déroulante qui contient le nom de tous les onglets, et qui permet de s'y rendre si l'on clique dessus, et un bouton dans tous les autres onglets qui permet de revenir à l'onglet de récap lorsque l'on clique dessus.

Je fais tout cela en VBA, ça marche très bien (tous les boutons et la liste fonctionnent), mais j'ai le problème suivant :
- si je sauvegarde le fichier créé immédiatement après que la macro ait tourné et que je le rouvre, j'ai le message : "Ce classeur a perdu son projet VBA, ses contrôles ActiveX et d'autres fonctionnalités liées à la programmabilité". Du coup, tous les boutons restent, mais ils ne font plus aucune action (cela devient juste des dessins quoi...)
- par contre, si je me sers d'au moins un des boutons avant de sauvegarder, lorsque je le rouvre je n'ai aucun problème

Du coup, j'ai deux alternatives :
- soit je trouve à quoi est du ce problème
- soit je trouve comment utiliser un des boutons avec VBA avant de sauvegarder et de fermer le fichier, ce qui permettrait de ne pas avoir à le faire à la main avant de sauvegarder (étant donné que je génère plusieurs fichiers à la suite)

Mes deux bouts de code qui sont appelés à chaque création d'onglet pour l'un, et une seule fois pour la liste, sont : 

Sub CreerBoutonRetour()

    Dim Code As String
    Dim NextLine As String
    Dim oOLE As OLEObject
    

    Set oOLE = ActiveWorkbook.ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
        Link:=False, DisplayAsIcon:=False, Left:=340, Top:=5, Width:=100, Height:=30)

    'Left position bouton par rapport au bord gauche de la feuille
    'Top position bouton par rapport au haut de la feuille
    'Width largeur bouton
    'Height hauteur bouton

 X = ActiveSheet.OLEObjects.Count 'compter le nombre de boutons existants dans la feuille

    'option nommer l'objet
    oOLE.Name = "CommandButton" & X

    'texte sur le bouton
    ActiveSheet.OLEObjects(1).Object.Caption = "Retour aux graphes"
    Code = "Sub CommandButton" & X & "_Click()" & vbCrLf
    Code = Code & "Sheets(""Graph (2)"").Select" & vbCrLf
    Code = Code & "End Sub"

    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        NextLine = .CountOfLines + 1
        .insertlines NextLine, Code
    End With

End Sub

Sub ListederoulanteOnglets()

    Dim Code As String
    Dim NextLine As String
    Dim oOLE As OLEObject
    

    Set oOLE = ActiveWorkbook.ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combobox.1", _
        Link:=False, DisplayAsIcon:=False, Left:=1000, Top:=500, Width:=200, Height:=30)

    'Left position bouton par rapport au bord gauche de la feuille
    'Top position bouton par rapport au haut de la feuille
    'Width largeur bouton
    'Height hauteur bouton

 X = ActiveSheet.OLEObjects.Count 'compter le nombre de boutons existants dans la feuille

    'option nommer l'objet
    oOLE.Name = "BoutonListe" & X

    'texte sur le bouton
    'ActiveSheet.OLEObjects(1).Object.Caption = "Choix de l'onglet"
    Code = "Sub BoutonListe" & X & "_DropButtonClick()" & vbCrLf
    Code = Code & "BoutonListe" & X & ".Clear" & vbCrLf
    
    sheetsnbre = ActiveWorkbook.Sheets.Count
    For cptouze = 1 To sheetsnbre
    Code = Code & "BoutonListe" & X & ".AddItem Sheets(" & cptouze & ").Name" & vbCrLf
    Next cptouze
    Code = Code & "End Sub"

    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        NextLine = .CountOfLines + 1
        .insertlines NextLine, Code
    End With
    
    Code2 = "Sub BoutonListe" & X & "_Click()" & vbCrLf
    Code2 = Code2 & "Sheets(" & "BoutonListe" & X & ".Value).Activate" & vbCrLf
    Code2 = Code2 & "End Sub"
    
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        NextLine = .CountOfLines + 1
        .insertlines NextLine, Code2
    End With

End Sub

Varaxor · Answer

Je pense que ce fil de discutions interresse pas mal de monde.

Voici comment nous avons résolu le problème :

On crée une macro avec les paramètres que l'on a à définir

ex : 

Sub ChangeSheet(SheetName as String)
 Sheets(SheetName).Select
End Sub

Puis l'on passe le paramètre dans l'appel du boutton créé dynamiquement :


sheet1.Shapes(1).OnAction = "'ChangeSheet ""Feuil1""'"

-- · Answer

tout vient du nom de la page :
essayer de faire, au debut de la fonction

Dim sheetName
sheetName = ActiveSheet.Name
Dim sheetIndex
sheetIndex = ActiveSheet.Index
Dim nameIndex
nameIndex = "Feuil" & sheetIndex
ActiveSheet.Name = nameIndex

et a la fin :

ActiveSheet.Name = sheetName

Créer un bouton dynamiquement excel via VBA

20 réponses

Discussions similaires

Newsletters