Excel VBA – Loop Through All Textboxes in UserForm and Clear the Values

vba excel

← PrevNext →

Tools you can use

UserForms in Excel usually have many controls specially used for data entry purpose and in that case, the form might have many textbox controls. While submitting the data, you realized that you have entered wrong values in the fields and you want to clear all the data. How quickly you can do that. I am sharing a simple example here on how to loop through all textboxes in a UserForm and clear all the values with the click of a button.

Let us assume, I have a UserForm with 3 textboxes and a button, to clear the boxes. On my Excel sheet however, I have another button (an ActiveX Control), which shows the UserForm.

Loop through textboxes in UserForm in Excel VBA and clear all values

Must read: Excel VBA - Dynamically create controls in UserForm and save data in Ms-Access

My UserForm

Adding controls to a UserForm is effortless. You can simply drag and drop controls from the Toolbox window.

Toolbox Window in Excel VBA UserForm

Add few textbox controls and a CommandButton control and name it ClearFields. You can name the textboxes if you wish. However, I am naming the boxes, since I’ll loop through all controls, check for textboxes (in particular), and clear the boxes.

On the UserForm, double click the button and write the below code inside the button’s click event.
Code Behind

Here, it will loop through each control on the UserForm and see if the control is a textbox. If yes, it would clear the value in it.

Private Sub ClearFields_Click()

    Dim ctrl As Control      ' CREATE A CONTROL OBJECT.

    ' LOOP THROUGH EACH CONTROL, CHECK IF THE CONTROL IS A TEXTBOX.
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            ctrl.Value = ""      CLEAR THE VALUE.
        End If
    Next ctrl
End Sub

' ASSIGN VALUES TO THE TEXTBOXES, WHEN FORM LOADS. (OPTIONAL)
Private Sub UserForm_Initialize()
    TextBox1.Value = "Arun"
    TextBox2.Value = "Computer"
    TextBox3.Value = "123456789"
End Sub
Note: Besides the button’s click event, I have UserForm_Initialize() procedure, where I am assigning values to the textboxes when the form load (or initializes). Now, this is optional. You can enter the values later and clear the values in each box.
Code to Open UserForm on Excel Sheet

As I said earlier, I have a Button (An ActiveX Control) on my Excel sheet, which will open the userform for data entry. You can write the code to open the userform inside the sheet1 window (since the ActiveX control is in sheet1).

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

Well, that’s it. Thanks for reading. ☺

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.
Delivered by FeedBurner