Assign code to a button created dynamically

Question

I'm trying to get a button I've created dynamically on an excel userform form to run a macro called transfer which I've written in Module 1 of the "Modules" section of my project.

Below I've pasted the code I've written so far in the userform which actually manages to create the Transfer to Sheet button in the frame (which I've also created dynamically) but for some reason, when I run VBA I get a 438 error message saying that Object doesn't support this property or method.

Can anybody tell me how I can resolve this?

Here's the code:

Dim framecontrol1 As Control

Set workitemframe = Controls.Add("Forms.Frame.1")
With workitemframe
    .Width = 400
    .Height = 400
    .Top = 160
    .Left = 2
    .ZOrder (1)
    .Visible = True
End With

workitemframe.Caption = "Test"
Set framecontrol1 = workitemframe.Controls.Add("Forms.commandbutton.1")

With framecontrol1
    .Width = 100
    .Top = 70
    .Left = 10
    .ZOrder (1)
    .Visible = True
    .Caption = "Transfer to Sheet"
End With
framecontrol1.OnAction = "transfer"

score 26 · Answer 1 · edited Aug 18 '14 at 13:00

26

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

enter image description here

edited Aug 18 '14 at 13:00

Jean-François Corbett

37,420
30
139
188

answered Apr 19 '12 at 09:13

Siddharth Rout

147,039
17
206
250

+1 I'm not completely sure that the button was meant to remain (my code) or disappear (this code) at end. The class module is a clever workaround to provide code to the new button – brettdj Apr 19 '12 at 22:58
Hi Siddharth, your example worked really well and the way you explained it was great, especially for a VBA rookie like me! I can't let you go though without getting you to explain what the cmdArray(i). part does in ReDim Preserve cmdArray(1 To i) Set cmdArray(i).CmdEvents = ctl_Command – Tony Catton Apr 20 '12 at 08:55
(Ooops...pressed enter before I had finished writing my message!) As I was saying, it would be good to understand what the cmdArray(i) is needed for so any explanation you can provide on that would be great. Thanks again for your help Siddharth! – Tony Catton Apr 20 '12 at 08:58
@TonyCatton: No No.. Let me go..... Just Joking :) `cmdArray(i)` creates a control array of commandbuttons and then assigns them a common event. `Redim` can be use to re-dimension the array. You use `Preserve` when you don't want to destroy the existing array including all existing data in the elements. If you don't use 'Preserve` then it destroys the existing array and existing elements. HTH – Siddharth Rout Apr 20 '12 at 09:07
@SiddharthRout Hello, I appreciate your answer on this issue and I'd like to ask how would you go about passing arguments to the click event handler? – Giovanni Di Toro Jun 04 '19 at 03:03

score 9 · Answer 2 · edited Apr 19 '12 at 19:25

9

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

edited Apr 19 '12 at 19:25

Doug Glancy

27,214
6
67
115

answered Apr 19 '12 at 11:14

brettdj

54,857
16
114
177

+ 1 Another way to approach the problem :) – Siddharth Rout Apr 19 '12 at 14:39
2

+1 I disagree with the VBAX rating of "easy" for this code though. (I also edited a mildly humorous typo in the first sentence.) – Doug Glancy Apr 19 '12 at 19:25
1

+ 1 @DougGlancy :) I agree with you. programming the VBE or writing class modules no way is 'easy' programming :) However I guess and I could be wrong, it is talking about "Ease of Use" rather than difficulty level of the code. For a newbie, if you give everything on a plate then that code will always be "Easy" for him/her to use and I guess this is that that page does :) – Siddharth Rout Apr 19 '12 at 19:38

Assign code to a button created dynamically

2 Answers2

Linked

Related