How to assign code to a dynamically created button?

Question

First of all: I am a beginner on VBA and I don't have a clue about how UserForms works.

That said I am trying to assing code to 3 dynamically created CommandButtons.

After some research come across this page and I just wrote a code to write the codes of the buttons. Problem is, I need to distribute this Workbook so this approach is not good anymore.

I reaserched a lot (1, 2, 3, 4) and came across this post. I tried to do the example that @SiddharthRout did but I was not sucessfull. I tried to understand how the ClassModule works but I couldn't (1, 2). I think a code just exactly the one @SiddharthRout would solve my problem but I can't manage to make it work on a normal module.

Long story short: I need a code to assing the codes to the CommandButtons without using extensibility (code that writes code).

EDIT
I want to create these buttons on a normal Sheet, not on a UserForm.

score 0 · Answer 1 · answered Jul 13 '15 at 10:26

Read this: http://scriptorium.serve-it.nl/view.php?sid=13

Sub MakeForm()
    Dim TempForm As Object ' VBComponent
    Dim FormName As String
    Dim NewButton As MSForms.CommandButton
    Dim TextLocation As Integer
    ' ** Additional variable
    Dim X As Integer

    'Locks Excel spreadsheet and speeds up form processing
    Application.VBE.MainWindow.Visible = False
    Application.ScreenUpdating = False
    ' Create the UserForm
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    'Set Properties for TempForm
    With TempForm
    .Properties("Caption") = "Temporary Form"
    .Properties("Width") = 200
    .Properties("Height") = 100
    End With
    FormName = TempForm.Name

    ' Add a CommandButton
    Set NewButton = TempForm.Designer.Controls _
    .Add("forms.CommandButton.1")
    With NewButton
    .Caption = "Click Me"
    .Left = 60
    .Top = 40
    End With
    ' Add an event-hander sub for the CommandButton
    With TempForm.CodeModule

    ' ** Add/change next 5 lines
    ' This code adds the commands/event handlers to the form
    X = .CountOfLines
    .InsertLines X + 1, "Sub CommandButton1_Click()"
    .InsertLines X + 2, "MsgBox ""Hello!"""
    .InsertLines X + 3, "Unload Me"
    .InsertLines X + 4, "End Sub"
    End With

    ' Show the form
    VBA.UserForms.Add(FormName).Show
    '
    ' Delete the form
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
  End Sub

Please explain in detail. The OP is a beginner and will need more help. — Priyesh, Jul 13 '15 at 10:53
Thank you for the answer, but baybe I was not clear enough. I want to create these buttons on a normal Sheet, not on a UserForm. — Hugo Cavalini Agudelo, Jul 13 '15 at 11:16

score 0 · Answer 2 · answered Jul 14 '15 at 11:13

This code from here:

Sub CreateButtons()
  Dim arrNames            As Variant
  Dim arrCaptions         As Variant
  Dim Wkb                 As Workbook
  Dim Wks                 As Worksheet
  Dim NewBtn              As OLEObject
  Dim Code                As String
  Dim NextLine            As Long
  Dim LeftPos             As Long
  Dim Gap                 As Long
  Dim i                   As Long

  'The Workbook, where...
  Set Wkb = ActiveWorkbook
  '... the worksheet is, where the button will be created
  ' and code will be written.
  Set Wks = Wkb.Worksheets(1)

  'Commandbuttons' CODENAMES in array
  arrNames = Array("cmbName1", "cmbName2", "cmbName3")
  'Commandbuttons' captions in array
  arrCaptions = Array("First Task", "Second Task", "Third Task")

  'Button pos.
  LeftPos = 100

  Gap = 15

  For i = LBound(arrNames) To UBound(arrNames)

    'Add a CommandButton to worksheet
    Set NewBtn = Wks.OLEObjects.Add(ClassType:="Forms.CommandButton.1")

    'Set button's properties
    With NewBtn
        .Left = LeftPos
        .Top = 5
        .Width = 65
        .Height = 30
        .Name = arrNames(i)
        .Object.Caption = arrCaptions(i)
        .Object.Font.Size = 10
        .Object.Font.Bold = True
        .Object.Font.Name = "Times New Roman"
    End With

    'Add the event handler code
    Code = "Sub " & NewBtn.Name & "_Click()" & vbCrLf
    Code = Code & "     MsgBox ""Hello...""" & vbCrLf
    Code = Code & "End Sub"

    '"Code" is a string
    With Wkb.VBProject.VBComponents(Wks.CodeName).CodeModule
        'Find last line in Codemodule
        NextLine = .CountOfLines + 1
        .InsertLines NextLine, Code
    End With

    'NEXT button's pos.
    LeftPos = LeftPos + NewBtn.Width + Gap

  Next i

End Sub

How to assign code to a dynamically created button?

2 Answers2