⭐ What Is a VBA Class Module and How Is It Used?
In Excel VBA, Class Modules allow you to create reusable and well-structured code by grouping properties, methods, and events into a single object.
Instead of writing repetitive procedures for each control (such as CheckBox or TextBox), a VBA Class lets you manage multiple controls dynamically with much cleaner code.
This approach is especially useful when working with UserForms that contain many controls, such as:
• Multiple CheckBoxes
• Dynamic TextBoxes
• Form elements created at runtime
• Repeating validation or formatting logic
Using classes makes your VBA projects easier to maintain, extend, and debug.
Instead of writing repetitive procedures for each control (such as CheckBox or TextBox), a VBA Class lets you manage multiple controls dynamically with much cleaner code.
This approach is especially useful when working with UserForms that contain many controls, such as:
• Multiple CheckBoxes
• Dynamic TextBoxes
• Form elements created at runtime
• Repeating validation or formatting logic
Using classes makes your VBA projects easier to maintain, extend, and debug.
⭐ Why Use VBA Classes with CheckBox and TextBox Controls?
When you place many CheckBox or TextBox controls on a UserForm, traditional VBA code quickly becomes messy:
• Each control needs its own event procedure
• Code duplication increases
• Small changes require edits in many places
By using a Class Module, you can:
• Handle all CheckBox events with a single class
• Apply the same logic to multiple TextBoxes
• Centralize validation, formatting, or visibility rules
• Reduce hundreds of lines of repetitive code
In short, one class can control many controls.
• Each control needs its own event procedure
• Code duplication increases
• Small changes require edits in many places
By using a Class Module, you can:
• Handle all CheckBox events with a single class
• Apply the same logic to multiple TextBoxes
• Centralize validation, formatting, or visibility rules
• Reduce hundreds of lines of repetitive code
In short, one class can control many controls.
⭐ How VBA Class Modules Work with UserForm Controls
The idea is simple :
1. Create a Class Module
2. Declare the control (CheckBox or TextBox) using WithEvents
3. Write the event logic once
4. Assign multiple controls to the same class at runtime
For example :
• A CheckBox class can automatically hide or unhide worksheet columns
• A TextBox class can validate input, format values, or trigger actions when changed
This pattern is commonly used in professional VBA projects to build scalable UserForms.
1. Create a Class Module
2. Declare the control (CheckBox or TextBox) using WithEvents
3. Write the event logic once
4. Assign multiple controls to the same class at runtime
For example :
• A CheckBox class can automatically hide or unhide worksheet columns
• A TextBox class can validate input, format values, or trigger actions when changed
This pattern is commonly used in professional VBA projects to build scalable UserForms.
⭐ When Should You Use This Approach?
VBA Class Modules are ideal if:
• Your UserForm has more than a few controls
• Controls behave similarly but act on different targets
• You want cleaner, reusable, and professional VBA code
• You plan to expand or reuse the project later
If your goal is to create downloadable VBA tools that others can easily use and extend, this structure is strongly recommended.
• Your UserForm has more than a few controls
• Controls behave similarly but act on different targets
• You want cleaner, reusable, and professional VBA code
• You plan to expand or reuse the project later
If your goal is to create downloadable VBA tools that others can easily use and extend, this structure is strongly recommended.
⭐ What You’ll Find in the Examples Below
In the samples provided on this page, you will find:
• VBA Class examples for CheckBox controls
• VBA Class examples for TextBox controls
• Practical UserForm implementations
• Ready-to-use code suitable for direct download
• Clean structure that can be adapted to your own projects
Each example demonstrates how Class Modules simplify UserForm logic while keeping the code readable and efficient.
• VBA Class examples for CheckBox controls
• VBA Class examples for TextBox controls
• Practical UserForm implementations
• Ready-to-use code suitable for direct download
• Clean structure that can be adapted to your own projects
Each example demonstrates how Class Modules simplify UserForm logic while keeping the code readable and efficient.
🔴 Hiding-Unhiding Worksheet Columns Using Userform
We created a different template by creating our own class in the VBA Window.In this template, a UserForm automatic load when the workbook is opened .There are a drop down list, buttons, checkboxes in this form.The workbook’s sheets are listed in the drop down list.The sheets of the workbook can be navigated through this drop-down list. The used columns in each worksheet are lined up with their headers in the UserForm. ↴
A checkbox for each column is created by the procedure in the module.When any checkbox is selected, the column corresponding to that checkbox is hidden by the procedure in the class module. VBA codes of module in UserForm : ⬇
Dim Buton1() As New Class_Controls
Sub auto_open() 'The Userform starts automatically when the Workbook is opened.
Frm_Controls.Show
End Sub
Sub op()
Dim sayi As Integer
Dim ctrl As Control
sayi = 0
For Each ctrl In Frm_Controls.Controls
If TypeName(ctrl) = "CheckBox" Then
sayi = sayi + 1
ReDim Preserve Buton1(1 To sayi)
Set Buton1(sayi).fd = ctrl
End If
Next ctrl
End Sub
📌 Module Code – What Does It Do?
This module is responsible for initializing and managing multiple CheckBox controls dynamically by linking them to a class module (Class_Controls).
Its main purpose is to enable event-driven behavior (Click events) for CheckBoxes placed on a UserForm.
Because VBA does not support events for control arrays natively, this structure is a common and effective workaround.
Its main purpose is to enable event-driven behavior (Click events) for CheckBoxes placed on a UserForm.
Because VBA does not support events for control arrays natively, this structure is a common and effective workaround.
🔹 Declaring the Class Array
Public WithEvents fd As MSForms.CheckBox
🔹 What does this line mean? ↴
Dim Buton1() As New Class_Controls
• Buton1() → Declares a dynamic array (size will be defined later)
• As New Class_Controls → Each element of the array is an instance of the class module Class_Controls
Namely : ✔ Buton1() is a dynamic array that stores multiple class objects,each one capable of handling events for a single CheckBox.
Buton1() → a dynamic array
Each element → an object created from the Class_Controls class
Purpose → to create a separate class instance for each CheckBox In other words :• Buton1(1) → listens to CheckBox1
• Buton1(2) → listens to CheckBox2
• Buton1(3) → listens to CheckBox3 This allows VBA to:• Track many CheckBoxes
• Handle their Click events individually
• Apply the same logic without duplicating code
• As New Class_Controls → Each element of the array is an instance of the class module Class_Controls
Namely : ✔ Buton1() is a dynamic array that stores multiple class objects,each one capable of handling events for a single CheckBox.
Buton1() → a dynamic array
Each element → an object created from the Class_Controls class
Purpose → to create a separate class instance for each CheckBox In other words :• Buton1(1) → listens to CheckBox1
• Buton1(2) → listens to CheckBox2
• Buton1(3) → listens to CheckBox3 This allows VBA to:• Track many CheckBoxes
• Handle their Click events individually
• Apply the same logic without duplicating code
🔹 What Does op() Do?
The op() procedure dynamically:
1.Scans all controls on the UserForm
2.Finds every CheckBox
3.Creates a new class instance for each CheckBox
4.Links the CheckBox to the class variable fd
5.Stores each class instance inside the Buton1() array
1.Scans all controls on the UserForm
2.Finds every CheckBox
3.Creates a new class instance for each CheckBox
4.Links the CheckBox to the class variable fd
5.Stores each class instance inside the Buton1() array
🔹 Why is this necessary?
In VBA:
• You cannot directly use WithEvents on multiple controls
• You cannot create event-handling control arrays This method solves both limitations.
• You cannot directly use WithEvents on multiple controls
• You cannot create event-handling control arrays This method solves both limitations.
🔹 Line-by-Line Key Explanation
🔸 For Each ctrl In Frm_Controls.Controls
Loops through every control on the UserForm.
Loops through every control on the UserForm.
🔸 If TypeName(ctrl) = "CheckBox" Then
Checks whether the current control is a CheckBox.
Checks whether the current control is a CheckBox.
🔸 ReDim Preserve Buton1(1 To sayi)
• Dynamically resizes the array
• Preserve keeps previously created objects intact
• Ensures each CheckBox gets its own class instance
• Dynamically resizes the array
• Preserve keeps previously created objects intact
• Ensures each CheckBox gets its own class instance
🔸 Set Buton1(sayi).fd = ctrl
This is the most important part.What happens here:• Buton1(sayi) → the current instance of Class_Controls
• .fd → a WithEvents CheckBox variable defined in the class module
• ctrl → the actual CheckBox control on the UserForm
This line connects the real CheckBox control to the class so that :
• Each CheckBox has its own event handler
• Click events are captured individually
• Dynamic control handling becomes possible 👉 Without this line, the CheckBoxes would exist, but their events could not be managed dynamically.
This is the most important part.What happens here:• Buton1(sayi) → the current instance of Class_Controls
• .fd → a WithEvents CheckBox variable defined in the class module
• ctrl → the actual CheckBox control on the UserForm
This line connects the real CheckBox control to the class so that :
• Each CheckBox has its own event handler
• Click events are captured individually
• Dynamic control handling becomes possible 👉 Without this line, the CheckBoxes would exist, but their events could not be managed dynamically.
Codes of class module : ⬇
Public WithEvents fd As MSForms.CheckBox Private Sub fd_Click() Dim a As Integer If fd.Value = True Then a = Replace(fd.Name, "CheckBox", "") Sheets(Frm_Controls.ComboBox1.Value).Cells(1, a).EntireColumn.Hidden = True Else a = Replace(fd.Name, "CheckBox", "") Sheets(Frm_Controls.ComboBox1.Value).Cells(1, a).EntireColumn.Hidden = False End If End Sub
📌 What This Class Module Does (Explanation)
This class module is designed to handle events for dynamically created CheckBox controls on a UserForm.
The variable fd is not a class name, but an object variable declared with WithEvents: ⬇
Public WithEvents fd As MSForms.CheckBox This means :
• fd represents one CheckBox control instance
• The class listens to events (like Click) of that CheckBox
• Multiple CheckBoxes can share the same class logic, each through its own class instance
The variable fd is not a class name, but an object variable declared with WithEvents: ⬇
Public WithEvents fd As MSForms.CheckBox This means :
• fd represents one CheckBox control instance
• The class listens to events (like Click) of that CheckBox
• Multiple CheckBoxes can share the same class logic, each through its own class instance
🔍 How It Works
• Each CheckBox on the form is assigned to an instance of this class.
• The CheckBox name is expected to follow a pattern like:
CheckBox1, CheckBox2, CheckBox3, ...• When a CheckBox is clicked, the fd_Click event fires automatically.
Inside the event:
• The numeric part of the CheckBox name is extracted.
• That number is treated as a column index.
• The selected worksheet is taken from Frm_Controls.ComboBox1.
• The corresponding column is then hidden or unhidden based on the CheckBox state.
• The CheckBox name is expected to follow a pattern like:
CheckBox1, CheckBox2, CheckBox3, ...• When a CheckBox is clicked, the fd_Click event fires automatically.
Inside the event:
• The numeric part of the CheckBox name is extracted.
• That number is treated as a column index.
• The selected worksheet is taken from Frm_Controls.ComboBox1.
• The corresponding column is then hidden or unhidden based on the CheckBox state.
✅ Logic Summary
• Checked → Hide the related column
• Unchecked → Show the related column
This approach allows the form to:
• Control Excel columns dynamically
• Avoid repetitive code for each CheckBox
• Scale easily when new CheckBoxes are added
• Unchecked → Show the related column
This approach allows the form to:
• Control Excel columns dynamically
• Avoid repetitive code for each CheckBox
• Scale easily when new CheckBoxes are added
❔Why Use a Class Module Here?
Using a class module with WithEvents makes the solution :
• Cleaner and more maintainable
• Fully event-driven
• Ideal for dynamic or large numbers of form controls
Without a class module, each CheckBox would require its own separate event procedure, leading to duplicated and harder-to-manage code.
• Cleaner and more maintainable
• Fully event-driven
• Ideal for dynamic or large numbers of form controls
Without a class module, each CheckBox would require its own separate event procedure, leading to duplicated and harder-to-manage code.
View of the Userform in the VBA Window : ↴
If the number of checkboxes added for each the used column exceeds the width of the UserForm, a scrollbar is automatically created for the UserForm. ↴
Our codes in UserForm_Initialize procedure that provide us with this feature : ⬇
If chkbx_width > Me.InsideWidth Then
With Me
.ScrollBars = fmScrollBarsHorizontal 'This will create a horizantal scrollbar
.ScrollWidth = chkbx_width + 50
End With
Else
Me.ScrollBars = fmScrollBarsNone
End If
All codes of the Userform_Initialize procedure with explanations : ⬇
Private Sub UserForm_initialize() Dim son_sutun, j As Long Dim sht As Integer With Me .Left = 0 'The location of userform on the screen is set. .Top = 0 End With For sht = 1 To ThisWorkbook.Worksheets.Count 'The sheets of workbook is added to combobox. ComboBox1.AddItem Sheets(sht).Name Next sht ComboBox1.Value = ActiveSheet.Name son_sutun = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column 'The used columns in the sheet are determined. For i = 1 To 1 'Creating check boxes For j = 1 To son_sutun Set chkBox = Frm_Controls.Controls.Add("Forms.CheckBox.1", "CheckBox" & j) With chkBox 'Features of the created checkboxes .Top = i * 19 .Left = (j * 70) - 65 .Font.Size = 11 .Caption = Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value End With chkbx_width = (son_sutun * 70) + 15 If chkbx_width > Me.InsideWidth Then With Me .ScrollBars = fmScrollBarsHorizontal 'This will create a horizantal scrollbar .ScrollWidth = chkbx_width + 50 End With Else Me.ScrollBars = fmScrollBarsNone End If Next j Next i Call op End Sub
💡 Use case : Ideal for dashboards or dynamic reports where users want to customize visible columns.
🔴 Another template created using the VBA Class Module :⬇
🔴 Percantage calculate on UserForm – other template created using the VBA Class Module :⬇
As seen in Excel UserForms above, operations (percentage finding, sum etc.) are performed automatically without pressing any button. Class module codes of the template above : ⬇
Public WithEvents txt As MSForms.TextBox
Private Sub txt_Change()
ToplamAliver
End Sub
Private Sub ToplamAliver()
On Error Resume Next
Dim nesne As Control
Dim Top1 As Double, Top2 As Double
Dim Ad As String
Dim No As Integer
With UserForm1
For Each nesne In .Controls
If TypeName(nesne) = "TextBox" Then
No = Right(nesne.Name, Len(nesne.Name) - 7)
Ad = "TextBox"
Select Case No
Case 1 To 3
Top1 = Top1 + VBA.Format(.Controls(Ad & No).Value, "#.00")
If .Controls(Ad & No).Value = "" Then
.Controls(Ad & No + 4).Value = ""
End If
.Controls(Ad & No + 4).Value = VBA.Format(.Controls(Ad & No).Value * (.TextBox9.Value / 100), "#0.00")
Case 5 To 7
Top2 = Top2 + VBA.Format(.Controls(Ad & No).Value, "#.00")
End Select
End If
Next
.TextBox4 = VBA.Format(Top1, "#.00")
.TextBox8 = VBA.Format(Top2, "#.00")
End With
End Sub
UserForm’s view and codes of UserForm in VB Editor Window : ⬇
Conclusion
Using VBA Class Modules with CheckBox and TextBox controls is one of the best ways to write scalable and professional Excel VBA applications.Once you understand this pattern, managing complex UserForms becomes much easier.
You can download the examples below and adapt them directly to your own Excel projects.
Post a Comment