Adding a command button dynamically and attaching code to it

[pabharathi2005]

Hi All,
I am trying to create a spreadsheet software to create fixtures for different games as a part of a project. I have this need to be able to create a command button and the code for this to execute this is in Modules > CommandButton1_Click(). I am able to add the button but not add the code to it. It is a lengthy code. Can anyone help me with this? The code I used to add the button is as follows:

Sub Create_Command_Button()
Dim oOLE As OLEObject
Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=723, Top:=16.5, Height:=44.25, Width:=94.5)
oOLE.Interior.Color = vbRed
oOLE.Placement = 2
oOLE.Object.Caption = "Make Fixtures"
oOLE.Object.******* = Module1.CommandButton1_Click()
End Sub

Any help is appreciated.
​

 

[pijaku]

Hi,

There is Two ways to do that.
The first and easiest is write your code dynamically.
Put this code in your Sheet's Module :

Option Explicit

Sub Create_Command_Button()
Dim oOLE As OLEObject
Const NAMEOFBUTTON As String = "myButtonTest"


If Not IsButtonExists(NAMEOFBUTTON) Then
    Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=723, Top:=16.5, Height:=44.25, Width:=94.5)
    oOLE.Interior.Color = vbRed
    oOLE.Placement = 2
    oOLE.Name = NAMEOFBUTTON
    oOLE.Object.Caption = "Make Fixtures"
    Create_Code NAMEOFBUTTON
Else
    MsgBox "Button " & NAMEOFBUTTON & " already exists in sheets " & Me.Name
End If


End Sub


Private Function IsButtonExists(strName As String) As Boolean
    With ActiveWorkbook.VBProject.VBComponents(Me.Name).CodeModule
        IsButtonExists = .Find("Private Sub " & strName & "_Click()", 1, 1, .CountOfLines, 1, False, False)
    End With
End Function


Private Sub Create_Code(strName As String)
Dim Code As String


Code = "Private Sub " & strName & "_Click()" & vbCrLf
Code = Code & "Call Module1.MacroClick" & vbCrLf
Code = Code & "End Sub"


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

And Put this code in a Module named "Module1" :

Sub MacroClick()
    'Here put your [COLOR=#333333]lengthy code[/COLOR]
    MsgBox "Yes"
End Sub

The second way is :
> put a frame in your sheet,
> add a button in this Frame
> add code to your button just like here :
https://www.mrexcel.com/forum/excel...age-inside-frame-worksheet-2.html#post4994445

 

[VBA Geek]

you can also add a simple button without overcomplicating things

Sub Create_Button()
Dim oOLE As Excel.Button
Set oOLE = ActiveSheet.Buttons.Add(Left:=723, Top:=16.5, Height:=44.25, Width:=94.5)
oOLE.Caption = "Make Fixtures"
oOLE.OnAction = "Sayhi"
End Sub


Sub sayHi()
    MsgBox "hi"
End Sub

 

[pabharathi2005]

Hi,

There is Two ways to do that.
The first and easiest is write your code dynamically.
Put this code in your Sheet's Module :

Option Explicit

Sub Create_Command_Button()
Dim oOLE As OLEObject
Const NAMEOFBUTTON As String = "myButtonTest"


If Not IsButtonExists(NAMEOFBUTTON) Then
    Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=723, Top:=16.5, Height:=44.25, Width:=94.5)
    oOLE.Interior.Color = vbRed
    oOLE.Placement = 2
    oOLE.Name = NAMEOFBUTTON
    oOLE.Object.Caption = "Make Fixtures"
    Create_Code NAMEOFBUTTON
Else
    MsgBox "Button " & NAMEOFBUTTON & " already exists in sheets " & Me.Name
End If


End Sub


Private Function IsButtonExists(strName As String) As Boolean
    With ActiveWorkbook.VBProject.VBComponents(Me.Name).CodeModule
        IsButtonExists = .Find("Private Sub " & strName & "_Click()", 1, 1, .CountOfLines, 1, False, False)
    End With
End Function


Private Sub Create_Code(strName As String)
Dim Code As String


Code = "Private Sub " & strName & "_Click()" & vbCrLf
Code = Code & "Call Module1.MacroClick" & vbCrLf
Code = Code & "End Sub"


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

And Put this code in a Module named "Module1" :

Sub MacroClick()
    'Here put your [COLOR=#333333]lengthy code[/COLOR]
    MsgBox "Yes"
End Sub

The second way is :
> put a frame in your sheet,
> add a button in this Frame
> add code to your button just like here :
https://www.mrexcel.com/forum/excel...age-inside-frame-worksheet-2.html#post4994445

Hi,
I tried the first method and I am getting an error 1004 saying "Programmatic Access to Visual Basic project cannot be trusted".
I am writing this code for a userform named UserForm1. Is that the problem?

 

[Kyle123]

Don't do this. If you need to add dynamic buttons to a userform there are better ways, for a worksheet use a forms button and VBA Geek's solution.

Writing code with code is 99/100 the wrong solution, and it requires any users of the spreadsheet to lower their security settings as you have discovered.

 

[jkpieterse]

Why not use a forms button and attach a macro to it? You can leave the macro in the VBAProject.

 

[pijaku]

Hi all,

Userform is not the same than Worksheet.

So I need answers to these questions :
1- You want add dynamically CommandButton on an UserForm or on a Worksheet?
2- How many CommandButton do you want to add?

 

[pabharathi2005]

Hi VBA Geek,
I did as you suggested and it worked like a charm. Thanks a lot.

 

[pabharathi2005]

Hi all,
Thanks a bunch for the prompt replies. I was able to do what I wanted.