A few words of thanks would be greatly appreciated.

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.


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
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
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: © 123RF.com


A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « How to Create a UserForm in a Class Module Using VBA », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
1 vote - 4.0/5
1 Comment

great code