dynamic form cmd buttons

[runflacruiser]

Hi.
I'd like to generate a dynamic form with x amount of command buttons vertically in the form.
Any help would be appreciated!
Thanks.
-John

[runflacruiser]

Hi.
Well, I created my dynamic form. I'm having trouble dynamically generating my events, namely the click events for the cmd buttons I created.

I added the reference to VBA Extensibility 5.3. I see many examples of doing this on the web for excel but I can't seem to get it to work with draw.

Any help would be great. Below is a code example used for excel that I have been trying to modify to DRAW.
-John

Sub MakeUserForm()
    Dim TempForm As Object
    Dim NewButton As MSForms.commandbutton
    Dim NewLabel As MSForms.Label
    Dim NewTextBox As MSForms.TextBox
    Dim NewOptionButton As MSForms.OptionButton
    Dim NewCheckBox As MSForms.CheckBox
    Dim X As Integer
    Dim Line As Integer
    Dim MyScript(4) As String

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False

    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)

    'Create the User Form
    With TempForm
        .Properties("Caption") = "My User Form"
        .Properties("Width") = 450
        .Properties("Height") = 300
    End With

    'Create 10 Labels
    For X = 0 To 9
        Set NewLabel = TempForm.designer.Controls.Add("Forms.label.1")
        With NewLabel
            .Name = "FieldLabel" & X + 1
            .Caption = "My Label " & X + 1
            .Top = 20 + (12 * X)
            .Left = 6
            .Width = 90
            .Height = 12
            .Font.Size = 7
            .Font.Name = "Tahoma"
            .BackColor = &H80FFFF
        End With
    Next

    'Create 10 Text Boxes
    For X = 0 To 9
        Set NewTextBox = TempForm.designer.Controls.Add("Forms.textbox.1")
        With NewTextBox
            .Name = "MyTextBox" & X + 1
            .Top = 20 + (12 * X)
            .Left = 100
            .Width = 150
            .Height = 12
            .Font.Size = 7
            .Font.Name = "Tahoma"
            .BorderStyle = fmBorderStyleSingle
            .SpecialEffect = fmSpecialEffectFlat
        End With
    Next

    'Create 10 Check Boxes
    For X = 0 To 9
        Set NewCheckBox = TempForm.designer.Controls.Add("Forms.checkbox.1")
        With NewCheckBox
            .Name = "MyCheck" & X + 1
            .Caption = ""
            .Top = 20 + (12 * X)
            .Left = 260
            .Width = 12
            .Height = 12
            .Font.Size = 7
            .Font.Name = "Tahoma"
            .BackColor = &HFF00&
        End With
    Next

    'Create 10 Labels -> result of Check Box
    For X = 0 To 9
        Set NewLabel = TempForm.designer.Controls.Add("Forms.label.1")
        With NewLabel
            .Name = "Result_Text" & X + 1
            .Caption = ""
            .Top = 20 + (12 * X)
            .Left = 280
            .Width = 150
            .Height = 12
            .Font.Size = 7
            .Font.Name = "Tahoma"
            .BackColor = &H80FFFF
        End With
    Next

    'Create Event Handler Code For Each Check Box
    '(True -> Upper Case of Text Box Value;False -> Lower Case of Text Box Value)
    For X = 0 To 9
        With TempForm.codemodule
            Line = .countoflines
            MyScript(0) = "Sub MyCheck" & X + 1 & "_Click()"
            MyScript(1) = "If .MyCheck" & X + 1 & " = true then"
            MyScript(2) = ".result_Text" & X + 1 & ".caption = ucase(.mytextbox" & X + 1 & ".value)"
            MyScript(3) = ".result_Text" & X + 1 & ".caption = lcase(.mytextbox" & X + 1 & ".value)"
            .insertlines Line + 3, MyScript(0)
            .insertlines Line + 2, "With Me"
            .insertlines Line + 3, MyScript(1)
            .insertlines Line + 4, MyScript(2)
            .insertlines Line + 5, "Else"
            .insertlines Line + 6, MyScript(3)
            .insertlines Line + 7, "End if"
            .insertlines Line + 8, "End With"
            .insertlines Line + 9, "End Sub"
        End With
    Next

    'Show the form
    VBA.UserForms.Add(TempForm.Name).Show

    'Delete the form (Optional)
    'ThisWorkbook.VBProject.VBComponents.Remove TempForm

End Sub

[shelbym]

Alright here is an example of how to do this. First Create a new Form and make sure it is named UserForm1. Place the following code in the form:

Dim cmdBArray() As New clsBtnClick

Private Sub UserForm_Initialize()
    For i = 1 To 5
        ReDim Preserve cmdBArray(i)
        Set cmdBArray(i).cmdB = Me.Controls.Add("Forms.CommandButton.1", "cmd_" & i)
        
        With cmdBArray(i).cmdB
            .Caption = "Button added " & i
            .Width = 80
            .Left = 10
            .Height = 18
            .Top = -10 + i * 20
        End With
    Next i
End Sub

Public Sub btnProc(i As Integer)
    MsgBox "Button clicked: " & i
End Sub

Now you need to create a new class module. Name the class module: clsBtnClick and place the following code in the module.

Public WithEvents cmdB As MSForms.CommandButton

Private Sub cmdB_Click()
    Dim i As Integer

    sName = cmdB.Name
    i = CInt(Val(Mid(sName, 5, 1)))
    Call UserForm1.btnProc(i)
End Sub

Run the form. What you should get is 5 dynamically created buttons, click on each one and you get a message box telling you what button was clicked. Oh, and you don't need the reference to VBA Extensibility 5.3, it just works! :-)

-Shelby

[runflacruiser]

Genius.
Thanks Shelby.
I didn't even think of events!
I'll try it. Thanks!
-John

PS. It would still be interesting to know about adding, modifying, etc, code in a module on the fly.