How to create UserForm: in Excel, VBA

Create a UserForm containing a dynamic number of controls and make sure they are being triggered by certain events. To achieve this result, we will use a UserForm and a class module, and assign any control dynamically created in the userform to the class module. Finally the aim of this tutorial is also to obtain a single module and make the calling function as simple as possible (limited to 2-3 lines of code). Read on for the code.

Prerequisites

Go to Excel Options > Trust Management > Macro Settings and make sure the following option is enabled: Trust access to the VBA project object model.

The code also requires the below references namely the Microsoft Forms 2.0 Object Library and Microsoft Visual Basic For Applications Extensibility 5.3. You can enable them by clicking on the Tools menu of the VBA editor and then on References.

The code

In this example, we'll create a userform containing two buttons. Upon clicking on these button, their Caption will be displayed in the code of the calling function.

The class module

Create a class module in your VBA project, name it as PremierExemple (ClassName property) and insert this code:

Option Explicit
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'Enable the following references (Tools > References)
    'Microsoft Forms 2.0 Object Library
    'Microsoft Visual Basic For Applications Extensibility 5.3
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Public maForm As Object                                        'Userform
Public WithEvents Bouton As MSForms.CommandButton              'Button
Public Dico As Object                                          'Objet Dictionnary = Object collection
Private Nom As String         'Nom => create or delete userform
Private Sub Class_Initialize()
'create class
   Set Dico = CreateObject("Scripting.dictionary")
End Sub
Public Function Value()
'The Value de notre Classe method allow the creation of the user form
'and returns a value
    NewUsf "Mon premier UserForm"  'creation of userform
    NewBouton "toto", "TOTO", 120, 30, 5, 5  'create the TOTO button
    NewBouton "titi", "TITI", 120, 30, 5, 35  'create the TITI button
    maForm.Show   'display userform
    On Error GoTo fin
    Value = maForm.Tag  'assign the value contained in the Tag of the userform to our function.
    Unload maForm
    Exit Function
fin:
End Function
Private Sub NewUsf(monCaption As String)
'Creation of userform
    Set maForm = ThisWorkbook.VBProject.VBComponents.Add(3) 
    Nom = maForm.Name
    VBA.UserForms.Add (Nom)
    Set maForm = UserForms(UserForms.Count - 1)
    With maForm
      .Caption = monCaption
      .Width = 150
      .Height = 100
    End With
End Sub
Public Sub NewBouton(Name As String, Caption As String, Width As Double, Height As Double, Left As Double, Top As Double)
'Creation of a Control Button
Dim Obj
    Set Obj = maForm.Controls.Add("forms.CommandButton.1")
    If Obj = True Then Exit Sub
    Dim cls As New PremierExemple
    Set cls.maForm = maForm
    Set cls.Bouton = Obj
    With cls.Bouton
        .Name = Name
        .Caption = Caption
        .Move Left, Top, Width, Height
    End With
    Dico.Add Name, cls
    Set cls = Nothing
End Sub
Private Sub Bouton_Click()
'event procedure for button click
   maForm.Tag = Bouton.Caption
   maForm.Hide
End Sub
Private Sub Class_Terminate()
'class deletion
   Dim VBComp As VBComponent
   Set Dico = Nothing            'delete all instances of our class=> all buttons
   If Nom <> "" Then             'if it is the userform (the unique instance  having the "Nom" property filled)
      Set VBComp = ThisWorkbook.VBProject.VBComponents(Nom) 'search
      ThisWorkbook.VBProject.VBComponents.Remove VBComp     'delete
   End If
End Sub

The calling function

The procedure for the calling function is greatly simplified... thanks to the class module, you have access to a userform and a Value method. It is returned in a simple way, using the below calling code:

Sub test()
Dim MyForm As New PremierExemple
MsgBox MyForm.Value
Set MyForm = Nothing
End Sub