How to Create a UserForm in a Class Module Using 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 claForm = 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

Image: © 123RFom