Creating Userform Controls At Runtime

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
Can you show us a sample of the code that doesn't work.
 

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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

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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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
 

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
365
Platform
Windows
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
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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

[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.

 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,097
Messages
5,484,651
Members
407,459
Latest member
DICKSON KIMEMIA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top