21
votes

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"
2

2 Answers

25
votes

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 hereenter image description here

9
votes

You need to add the code to the UserForm programatically. I used my code from this vbax article as the reference

The code below:

  1. Runs from a normal module
  2. Adds the button to a UserForm called UserForm1
  3. 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