It is just a scenario. See the image of a UserForm. 👇
The form has few controls. I want to check the type of control (especially the checkbox) before submitting the data.
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.
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
Happy coding. ☺