VBA - How to Create a UserForm in a Class Module

September 2016

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

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

Related :

This document entitled « VBA - How to Create a UserForm in a Class Module » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.