create a button dynamically in Excel using VBA

Question

Bonjour,
I want to create a button using a macro and also assign a macro to the created button, knowing that the code related to the assigned macro should be dynamically changeable based on the content of certain cells.
I wrote the following code:
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 'create workbook
Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=340, Top:=30, Width:=100, Height:=30)
'Left position of button relative to the left edge of the sheet
'Top position of button relative to the top of the sheet
'Width of button
'Height of button

X = ActiveSheet.OLEObjects.Count 'count the number of existing buttons on the sheet

'optional to name the object
oOLE.Name = "CommandButton" & X
'text on the button
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

this code works very well when creating a new sheet "Workbooks.Add 'create workbook" after the loop.
Pressing the button takes me to the sheet "feuil2"

however, as soon as we remove the line "Workbooks.Add 'create workbook"
which allows creating the button on the active sheet, it no longer works and I get the following message:
"Cannot enter break mode now"
then if I continue: message: the index does not belong to the selection"

Can someone help me on this topic
thank you in advance.

Anonymous user · Accepted Answer

Hello,

I made some modifications so that it works on my end!

 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 'create workbook Set oOLE = ActiveWorkbook.ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False, Left:=340, Top:=30, Width:=100, Height:=30) 'Left position button relative to the left edge of the sheet 'Top position button relative to the top of the sheet 'Width button width 'Height button height X = ActiveSheet.OLEObjects.Count 'count the number of buttons existing in the sheet 'option name the object oOLE.Name = "CommandButton" & X 'text on the button 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

Anonymous user · Answer

Hello,

you need to add this syntax:

 With myCommandButtonDetail .Name = "CommandButtonDetail_" & iNbReappro .OnAction = "'Name of the routine to call'" '-- the rest of the params End With

Note, the line reads:

.OnAction = "'Name of the routine to call'"

The name of the routine must be between single quotes ' ... '
and the whole must be in double quotes " ' ... ' "

Lupin

Naya · Answer

Hello,

he tells me that the method or property is not supported by this object

I tried using the OLEObject as you did before but it doesn't work because I want to place the object on a form and it doesn't work:

when I do this (for testing) it works:
Set oOLECommandButtonDetail = ActiveWorkbook.ActiveSheet.OLEObjects.Add(arguments...)

but when I do this (what I want) it doesn't work:
Set oOLECommandButtonDetail = GestionMeuleForm.MultiPage1.Pages(4)._
CalculBesoinFrame.OLEObjects.Add(arguments...)

thank you for your response

Claire

Anonymous user · Answer

Hello,

First of all, I must admit that I confused
a menu button with the type of button you are trying to install.

I tested your code by modeling it to create an example for myself
and it seems to be a known bug from Microsoft according to
the research I did.

I'm sorry I can't go any further!

Lupin

Anonymous user · Answer

re:

Here is the code I tested:

 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

Hello,

In my case, I create a sheet dynamically in the code and I create a button in it, but when I want to insert code at the line:
With ThisWorkbook.VBProject.VBComponents(IntituleCre).CodeModule

It gives me the error:
"Runtime error 9:
The index is out of range"

And this happens no matter what I put in the with (even when the with is empty ...)

Did I forget something?

Anonymous user · Answer

Hello Stim,

With ThisWorkbook.VBProject.VBComponents(IntituleCre).CodeModule

should read:

With ThisWorkbook.VBProject.VBComponents("IntituleCre").CodeModule

unless [ IntituleCre ] is a variable and in that case, it
has not been initialized.

Lupin

Stim · Answer

Thank you for the clarification, Lupin ;)

But the problem is not there actually ^^ (IntituleCre is indeed a variable that contains the name of the sheet)

I just saw (by listing the vbcomponents) that the component is actually called "Feuil6" and it works very well when I use that name... so what I need is a method that returns the VB name of this sheet...

Anonymous user · Answer

re :

of the kind:

 Sub SearchSheet() 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 '

Lupine

stim · Answer

In fact, more simply, I found the function "ActiveSheet.CodeName" which allows me to retrieve the internal name of the sheet, as requested by the VBComponents function...

After wondering why I can't directly use the external name of the sheet... it surpasses me ^^

Thanks anyway for all your answers Lupin ;)

++

xaphie · Answer

Bonjour

I am not very strong in VBA on Excel, so I would need a little help.
I will explain a bit about the structure of my file.

I have a config sheet that allows me to create all the other sheets in the workbook.
In these sheets, I have a sheet that serves as a "base" where I want to create buttons dynamically based on the names given in my config sheet.
These buttons should take me directly to the chosen sheet
for example: a button named "group" should send me to the "group" sheet.
My problem is that I do not know how to retrieve the name of the button.

Here is the code I am considering

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

Thank you for your help.

Anonymous user · Answer

Hello,

Assuming that the config sheet is named [ Base ] !
Assuming that the button is placed on the Base sheet and is named [ Groupe ] !

Place this code behind the sheet:

 Option Explicit Private Sub Groupe_Click() Call Module1.Choix_Feuille(Me.Groupe.Name) End Sub

and place this code in a module named [ 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

Hello

Thank you for this response

Actually, what I would like to do is to retrieve the name of the button that is called "group" in the variable Nom_feuille of my macro below.

Sub Choix_Feuille(Nom_Feuille As String)

Nom_Feuille = ??? button_name_you_clicked_on ??????

If (Feuille_Existe(Nom_Feuille)) Then
Sheets(Nom_Feuille).Select
Range("A1").Select
Else
MsgBox "Impossible, the sheet does not exist"
End If

End Sub

I would like to do it this way, because I have a macro that creates my buttons based on a database (the config sheet). So each button has a name that corresponds to a sheet and all the buttons point to the macro "choix_feuille" above. That’s why I want to retrieve the name of the button so that when I click on the button group1, I go to the sheet group1; if I click on group2, I go to group2, and so on.

I didn’t quite understand how your solution works, but I think it requires knowing the number of buttons and creating a macro for each button. If I understood correctly, your solution doesn’t quite fit what I want to do because I don’t know the number of buttons that will be created since they are created from the config sheet, and therefore I can’t create as many macros as there are buttons. Hence the solution I would like to implement, but I am stuck on how to retrieve the name of the button that I clicked on.
Moreover, this allows the buttons to be dynamic.

Thank you for your help; if you see another way to achieve what I want, I am all ears.

Anonymous user · Answer

re:

There is a problem!!!

In VBA, it is impossible to create a control group, so each button
leads to a distinct routine!

For example:

If the first button is named [Group1]
When you click on the button, you arrive
at the procedure:

Private Sub Group1_Click()
End Sub

Thus, you call your procedure by passing the name of the button.

Private Sub Group1_Click()
Call Module1.Choix_Feuille("Group1")
End Sub
'

If your buttons are added dynamically, you will also
need to insert the corresponding code dynamically!

Lupin

guiss · Answer

Hello,

I'm taking my first steps alone in Visual Basic and I'm trying to understand a series of simple exercises that I'm starting to do (with a bit of difficulty...)

So I'm trying to create buttons on my Excel sheet via Visual Basic as part of the macro.

Basically, it's an exercise on a function to calculate a circle in which I've obviously encoded the value of pi, that of the radius, ... but I can't figure out how to display the buttons I'm trying to create (like appel_u_calcul, appel_p_calcul) and how to display a button "bouton form appel usf".

Because I'm having trouble creating and thus calculating with userform

In short, it's a tough start; thanks for the help given to this little message from a desperate earthling.

Anonymous user · Answer

Hello,

If I understand correctly, you are trying to create a button(s) on a sheet dynamically.

Start by performing the actions once using the macro recorder!
Excel Menu // Tools / Macro / New macro
Break down your actions into several small macros.

Example
Create a button
Create the associated code (the calculation function)!
Attach the pieces
Streamline the macros
Create the routine

In Excel
Alt F11

Copy/paste the code generated by the recorder and I will guide you for the
clean-up and the addition of loops and conditions.

Initially, I can advise you to use the workbook I published on Excel-DownLoad
https://www.excel-downloads.com/resources/categories/utilitaires.18/
Search for VizioXla, you will have a base model for almost all Excel objects!

Lupin

chlore · Answer

Good evening,

I am encountering the same kind of issue as the previous people, let me explain:
in a tab of my Excel workbook, I dynamically create buttons associated with a macro that allows me to display filtered data in another tab based on the project number listed. All my buttons lead to the same macro, which should be able to identify the row where the clicked button is placed, but I can't find how to retrieve the row in order to know what to filter for my output.
Thank you in advance for your help.

Wackolas · Answer

Bonjour,

I only found this topic that somewhat matches my needs, so I'm asking my question here as well.

I have an Excel macro that creates about twenty sheets and a summary sheet, and I used your advice to create a drop-down list in the summary sheet that contains the names of all the sheets, allowing users to jump to them by clicking, along with a button in all other sheets that lets users return to the summary sheet when clicked.

I’m doing all this in VBA, and it works great (all the buttons and the list function properly), but I have the following problem:
- If I save the file created immediately after the macro has run and then reopen it, I get the message: "This workbook has lost its VBA project, ActiveX controls, and other programmable features." As a result, all the buttons remain, but they no longer perform any action (they just become drawings...)
- However, if I use at least one of the buttons before saving, when I reopen it, I have no problems.

So I have two alternatives:
- Either I find out what is causing this problem
- Or I find out how to use one of the buttons with VBA before saving and closing the file, which would allow me to avoid doing it manually before saving (since I generate several files in succession).

My two pieces of code that are called each time a sheet is created for one, and only once for the list, are:

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 button relative to the left edge of the sheet 'Top position button relative to the top of the sheet 'Width button width 'Height button height X = ActiveSheet.OLEObjects.Count 'count the number of buttons existing in the sheet 'option name the object oOLE.Name = "CommandButton" & X 'text on the button ActiveSheet.OLEObjects(1).Object.Caption = "Return to graphs" 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 button relative to the left edge of the sheet 'Top position button relative to the top of the sheet 'Width button width 'Height button height X = ActiveSheet.OLEObjects.Count 'count the number of buttons existing in the sheet 'option name the object oOLE.Name = "BoutonListe" & X 'text on the button 'ActiveSheet.OLEObjects(1).Object.Caption = "Choice of the sheet" 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

I think this discussion thread interests quite a few people.

Here’s how we solved the problem:

We create a macro with the parameters that we need to define

for example:

Sub ChangeSheet(SheetName as String)
Sheets(SheetName).Select
End Sub

Then we pass the parameter in the call of the dynamically created button:

sheet1.Shapes(1).OnAction = "'ChangeSheet ""Sheet1""'"

-- · Answer

Everything comes from the name of the page:
try to do, at the beginning of the function

Dim sheetName
sheetName = ActiveSheet.Name
Dim sheetIndex
sheetIndex = ActiveSheet.Index
Dim nameIndex
nameIndex = "Sheet" & sheetIndex
ActiveSheet.Name = nameIndex

and at the end:

ActiveSheet.Name = sheetName

Create a button dynamically in Excel using VBA

20 answers

Download impossible for visual petanque

Reinstallation of w11 impossible after pc crash...

Connect external dvd player to samsung tablet

Phone not receiving certain sms

Images from videos - retrieval

Chkdsk /f and sfc /scannow okay, but startup impossible.

Recover deleted messages

How to change your phone number on le bon coin

Lost tnt channels (via antenna)

Taskbar that hides