There are various controls we can use inside a vba userform. When we design user interface, we use relevant controls according to the requirements of the user. We don’t need all the controls for all the applications we create. We put the controls according to how the user want to input, process and output data. Also there are some special controls like “Image” and “Scroll Bar”. This userform contains some widely used controls we use in our applications.
I haven’t put the controls in a meaningful order. Because I only created this to explain you how to access the each of these controls. Otherwise these controls should properly labeled and placed in a meaningful way. Also I didn’t use the conventional naming method to name the controls. These controls have their default names.
In some advance applications, we need to loop through all of these controls and take the actions according to the conditions. So in this post I will show you how to loop through all the controls of a userform. First, let’s try to loop through all the controls and print the name of each of the controls in immediate window.
|
Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls Debug.Print c.Name Next End Sub |
I have written the code in CommandButton1. So if we click the button we will get following result.
Sometimes we need to detect particular control types of a userform. So if we need to do that first we need to find a way to detect type of each of the controls. We can do it like this. This code will output control name with it’s type.
|
Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls Debug.Print "Name - " & c.Name & " Type - " & TypeName(c) Next End Sub |
You will get following result when you click the button.
Then if you modify this code little bit, you can read the values of only particular controls. Following example will print the values of textboxes.
|
Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls If StrComp(TypeName(c), "Textbox", vbTextCompare) = 0 Then Debug.Print c.Value End If Next End Sub |
First I entered some text before click the button.
When you click on the CommandButton1 you will get this result.
