Creating Userform Controls At Runtime

[HighAndWilder]

Good morning

I am adding controls at runtime to a userform.

I have done this before and I cannot work out what I am doing differently this time.

I can add the controls alright and I can add the event handler code in the form module programatically. The form loads alright but the event handlers do not work.

No errors are reported.

All I can assume is that the Control does not know that the Event Handler exists.

What I find interesting is that when, for example, a Command Button is set up manually and the Event Handler code is written from scratch rather than clicking on the button then the Event Handler code does not run when the user clicks on the button at runtime.

Has anybody else come across this problem?

Thanks

 

[Jaafar Tribak]

Can you show us a sample of the code that doesn't work.

 

[HighAndWilder]

Can you show us a sample of the code that doesn't work.

If you paste this code into the form module and load the form. The form is to be called frmUserForm.

The 'Test' button will not work when you first load the form but load it again and it will work. The Submit button will not work.

Prior to subsequent testing you will have to delete the 'cmdButton_Click' sub at the bottom of the form code module.

I am sure that the solution is simple and that I am just missing something.

Thanks for looking.

Option Explicit

Private Sub cmdTestIfCodeExists_Click()

    Call cmdButton_Click

End Sub

Private Sub UserForm_Initialize()
Dim q As String
Dim ctrl As Control
Dim strCodeString As String

    Me.Caption = "Form Caption"

    q = Chr(34)
        
    Set ctrl = frmUserForm.Controls.Add("Forms.CommandButton.1")
    With ctrl
        .Top = 20
        .Left = 20
        .Width = 60
        .Height = 25
        .Caption = "Submit"
    End With
            
    strCodeString = "Private Sub cmdButton_Click()" & vbCrLf & _
    "    MsgBox " & q & "Hello World" & q & vbCrLf & _
        "End Sub"
    
    Call subCreateProcedure(Me.Name, strCodeString)
    
    ActiveWorkbook.Save
    
End Sub

Private Sub subCreateProcedure(strFormName As String, strCodeString As String)
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim i As Integer
        
    Set VBComp = ThisWorkbook.VBProject.VBComponents(strFormName)
   
    Set CodeMod = VBComp.CodeModule
    
    LineNum = CodeMod.CountOfLines + 3
    
    'For i = 1 To 1
        CodeMod.InsertLines LineNum + 1, ""
        LineNum = LineNum + 1
    'Next i
    
    CodeMod.InsertLines LineNum, strCodeString

End Sub

' **************************************************************************************************

 

[Jaafar Tribak]

How about not using the Visual Basic Extensibility library and just sinking the newly created commandbutton at runtime ?

Are you happy with this :

In the UserForm Module:

Option Explicit

Private WithEvents CButton As MSForms.CommandButton


Private Sub UserForm_Initialize()

    Me.Caption = "Form Caption"
        
   With Me.Controls.Add("Forms.CommandButton.1")
        .Top = 20
        .Left = 20
        .Width = 60
        .Height = 25
        .Caption = "Submit"
    End With
    
End Sub

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    Set CButton = Control
End Sub

Private Sub CButton_Click()
    MsgBox "Hello World"
End Sub

 

[HighAndWilder]

Hi Jaafar

That works fine, thank you very much.

I assume that I will need to insert a Private WithEvents declaration line for each control created and that works fine unless there is a way that avoids doing this.

I will also need to enhance the Add_Control Event Handler, example below, to accomodate all of the controls added.

I can do both of these programatically.

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
    
    Select Case Control.Name
        Case "cmdSubmit":
            Set cmdSubmit = Control
        Case "cmdReports":
            Set cmdReports = Control
    End Select

End Sub

 

[Jaafar Tribak]

If you just need to add two commandbuttons then yes just a Private WithEvents declaration line for each control created and adapt the Add_Control Event Handler as you did but you need to set the control name when adding it :

With Me.Controls.Add("Forms.CommandButton.1", "cmdSubmit")

Or you could use this alternative:

Option Explicit

Private WithEvents cmdSubmit As MSForms.CommandButton
Private WithEvents cmdReports As MSForms.CommandButton


Private Sub UserForm_Initialize()

    Me.Caption = "Form Caption"
        
   Set cmdSubmit = Me.Controls.Add("Forms.CommandButton.1", "cmdSubmit")
   
   With cmdSubmit
        .Top = 20
        .Left = 20
        .Width = 60
        .Height = 25
        .Caption = "Submit"
        
    End With    
    
    Set cmdReports = Me.Controls.Add("Forms.CommandButton.1", "cmdReports")
    
    With cmdReports
        .Top = 20
        .Left = 120
        .Width = 60
        .Height = 25
        .Caption = "Reports"
    End With  
    
End Sub

[COLOR=#008000]'Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
'
'    Select Case Control.Name
'        Case "cmdSubmit":
'            Set cmdSubmit = Control
'        Case "cmdReports":
'            Set cmdReports = Control
'    End Select
'
'End Sub[/COLOR]

Private Sub cmdSubmit_Click()
    MsgBox Me.ActiveControl.Name
End Sub

Private Sub cmdReports_Click()
    MsgBox Me.ActiveControl.Name
End Sub

Note:
Remember that these created controls at runtime will not be saved with the workbook ... To save the added commandbuttons, you will need to use the Designer object of the userform component.