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.
Excel VBA - Dynamically create controls in UserForm and save data in Ms-Access
Adding controls to a UserForm is effortless. You can simply drag and drop controls from the Toolbox window.
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
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. ☺