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.
Adding controls to a UserForm is effortless. You can simply drag and drop controls from the Toolbox window.
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.
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.
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.