Form creation using vba instead of creating it ahead of time

[R23_Wave]

hello smart people,

i am in need of some help i am trying to make a user form that can change based on needs but for some reason the program isn't running when i click the button. if you can fix this code i can then implement that fix into my larger program.

General Declarations section of the workbook
Option Explicit

Private Sub Workbook_Open()
UserForm1.Show
End Sub


UserForm Initialize section
Private Sub UserForm_Initialize()

Me.Caption = "Create Folder Form"
Me.Width = 300
Me.Height = 150

Dim lblPrompt As MSForms.Label
Set lblPrompt = Me.Controls.Add("Forms.Label.1", "lblPrompt")
With lblPrompt
.Caption = "Enter the WO# below:"
.Left = 20
.Top = 20
.Width = 200
.Height = 20
End With

Dim txtInput As MSForms.TextBox
Set txtInput = Me.Controls.Add("Forms.TextBox.1", "txtInput")
With txtInput
.Left = 20
.Top = 40
.Width = 200
.Height = 20
End With

Dim btnCreateFolder As MSForms.CommandButton
Set btnCreateFolder = Me.Controls.Add("Forms.CommandButton.1", "btnCreateFolder")
With btnCreateFolder
.Caption = "Create Folder"
.Left = 20
.Top = 70
.Width = 100
.Height = 30
End With

End Sub

Private Sub btnCreateFolder_Click()

MsgBox "hello"

End Sub

 

[John_w]

For the dynamic command button on the userform, it's not as straightforward as one might think.

I followed the answer here:

Programmatically adding a commandbutton to a userform

In excel vba I have added a commandbutton to userform... like below Set ctrl = Me.Controls.Add( _ bstrProgID:="Forms.CommandButton.1", _ Name:="CommandButton1", Visible:=True) N...

stackoverflow.com

As well as adding the DynBtn class, I also made the following changes to your UserForm1 module:

1. Declare the btnCreateFolder_Click procedure as Public Sub btnCreateFolder_Click() instead of Private, so that the DynBtn class handler can see it and therefore call it.

2. Move the Dim txtInput As MSForms.TextBox line to the top of the UserForm module, so that btnCreateFolder_Click can read the txtInput.Value property.

Here's the complete UserForm1 module which uses the above answer and works for me:

Option Explicit

Dim mBtn(0) As DynBtn
Dim txtInput As MSForms.TextBox

Private Sub UserForm_Initialize()

    Me.Caption = "Create Folder Form"
    Me.Width = 300
    Me.Height = 150
  
    Dim lblPrompt As MSForms.Label
    Set lblPrompt = Me.Controls.Add("Forms.Label.1", "lblPrompt")
    With lblPrompt
        .Caption = "Enter the WO# below:"
        .Left = 20
        .Top = 20
        .Width = 200
        .Height = 20
    End With
  
    Set txtInput = Me.Controls.Add("Forms.TextBox.1", "txtInput")
    With txtInput
        .Left = 20
        .Top = 40
        .Width = 200
        .Height = 20
    End With
 
    Set mBtn(0) = New DynBtn
    With mBtn(0).Load(Me, Me.Controls.Add("Forms.CommandButton.1", "btnCreateFolder", True), "btnCreateFolder_Click").Object
        .Caption = "Create Folder"
        .Left = 20
        .Top = 70
        .Width = 100
        .Height = 30
    End With
  
End Sub

'Note: procedure declared as Public, not Private
Public Sub btnCreateFolder_Click()
    MsgBox txtInput.Value
End Sub

Private Sub UserForm_Terminate()
    'Erase mBtn  'crashes Excel
    Set mBtn(0) = Nothing
End Sub

Note: The SO answer's UserForm_Terminate crashed my Excel and I fixed it as shown above.