Check if control in UserForm is a Checkbox using VBA

excel vba

← PrevNext →

Let us assume I have a UserForm in my Excel worksheet, which has few controls (or elements like textbox, label, checkbox etc.). How do I check if the control is a checkbox using a VBA macro? Let’s find out.
The UserForm

It is just a scenario. See the image of a UserForm. 👇

Check if the control is a Checkbox using VBA

The form has few controls. I want to check the type of control (especially the checkbox) before submitting the data.

The VBA Macro

To check the type of control using a Macro, I need to first loop through all the controls in the UserForm.

Option Explicit

Dim objCtrl As Control

For Each objCtrl In Me.Controls
    If TypeName(objCtrl) = "CheckBox" Then
        objCtrl.Value = Not objCtrl.Value
    End If
Next objCtrl

• First, I have created an object of Control collection. The object objCtrl now has references of all the controls on a form.

• Next, I’ll loop through each object or control. Using the function TypeName(), I can check if the control is a checkbox or not. You can do a similar check for textbox or other controls on a form.

👉 Do you know you can dynamically create controls in a UserForm and save the data? Yes, you can.
Dynamically create and add controls using VBA

TypeName() Syntax

TypeName(varname)

The built-in TypeName() function returns a string that provides information about a variable. The information usually is the variable type. For example,

Dim sName As String
sName = "Arun Banik"
Debug.Print TypeName(sName)

The output will be string. That’s what I have defined. The variable “sName” is a string. Try with other variable types and see the result.

The TypeName() function also takes a parameter as object. That’s exactly how I got the control type in the first example.

If TypeName(objCtrl) = "CheckBox" Then
    objCtrl.Value = Not objCtrl.Value
End If

Well, here’s another way you can check if the control is a checkbox using VBA.

Dim i As Integer

For i = 0 To UserForm1.Controls.Count - 1
    'Debug.Print (UserForm1.Controls.Item(i).Name)
        
    If TypeName(UserForm1.Controls.Item(i)) = "CheckBox" Then
        UserForm1.Controls.Item(i).Value = Not UserForm1.Controls.Item(i).Value
    End If
Next i

← PreviousNext →