Assign code to a button created dynamically
Solution 1:
Here is an example. Please amend it to suit your needs :)
This example will create a command button and assign code to it so that when it is pressed, it will display "Hello World".
Paste this code in the click event of a command button which will create a new command button dynamically and assign code to it.
Option Explicit
Dim cmdArray() As New Class1
Private Sub CommandButton1_Click()
Dim ctl_Command As Control
Dim i As Long
i = 1
Set ctl_Command = Me.Controls.Add("Forms.CommandButton.1", "CmdXYZ" & i, False)
With ctl_Command
.Left = 100
.Top = 100
.Width = 255
.Caption = "Click Me " & CStr(i)
.Visible = True
End With
ReDim Preserve cmdArray(1 To i)
Set cmdArray(i).CmdEvents = ctl_Command
Set ctl_Command = Nothing
End Sub
and paste this code in a class module
Option Explicit
Public WithEvents CmdEvents As MSForms.CommandButton
Private Sub CmdEvents_Click()
MsgBox "Hello Word"
End Sub
SNAPSHOT
Solution 2:
You need to add the code to the UserForm programatically. I used my code from this vbax article as the reference
The code below:
- Runs from a normal module
- Adds the button to a UserForm called UserForm1
-
Adds this code to the Userform for a Click Event
Private Sub CommandButton1_Click() Call Transfer End Sub
VBA from normal module
Sub AddToForm()
Dim UF As Object
Dim frameCOntrol1 As Object
Set UF = ActiveWorkbook.VBProject.VBComponents("UserForm1")
Set frameCOntrol1 = UF.designer.Controls.Add("Forms.CommandButton.1")
With frameCOntrol1
.Width = 100
.Top = 70
.Left = 10
.ZOrder (1)
.Visible = True
.Caption = "Transfer to Sheet"
End With
With UF.CodeModule
.InsertLines 2, _
"Private Sub " & frameCOntrol1.Name & "_Click()" & Chr(13) & _
"Call Transfer" & Chr(13) & _
"End Sub"
End With
End Sub