Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Loop Through All the Controls of a VBA UserForm

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.

Contact Form

Name

Email *

Message *

Popular Posts