Creating Userform Controls At Runtime
Results 1 to 6 of 6

Thread: Creating Userform Controls At Runtime

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating Userform Controls At Runtime

    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

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,305
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating Userform Controls At Runtime

    Can you show us a sample of the code that doesn't work.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Userform Controls At Runtime

    Quote Originally Posted by Jaafar Tribak View Post
    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.

    Code:
    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
    
    ' **************************************************************************************************

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,305
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating Userform Controls At Runtime

    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:
    Code:
    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
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Userform Controls At Runtime

    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.

    Code:
    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

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,305
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating Userform Controls At Runtime

    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:
    Code:
    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
    
    '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
    
    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.

    Last edited by Jaafar Tribak; Aug 17th, 2019 at 12:26 PM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •