How to use a textbox change event with Excel VBA to enter data of specific length into worksheet quickly and easily. Watch the video below:
We can automate the process of entering data into an Excel worksheet using a macro and a simple user-form. The complete VBA code is given below:
Module 1
Sub OpenUserForm()
UserForm1.Show
End Sub
UserForm1
Macro Code for the UserForm Controls:
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub
Private Sub TextBox1_Change()
If Len(TextBox1) = 10 Then
ActiveCell = TextBox1
ActiveCell.Offset(1, 0).Activate
TextBox1 = “”
End If
End Sub
VBA macro code when the workbook fires up:
Private Sub Workbook_Open()
MsgBox “Please select the cell where you wish to start entering the data.”
End Sub
When the workbook opens we ask the user to select a cell where he wishes to start entering data. Let’s say the user selects the worksheet cell A2. Now we can click on the form control button on the Excel worksheet to display the user-form. Here we can enter a mobile number, a student’s roll number or an ID. We assume that we will always enter data of a specific length like a mobile number with 10 digits. As soon as the 10th digit is input into the text-box on the user-form, the data is automatically transferred to a particular cell in the Excel worksheet and the cursor now moves to another cell. We can now enter more data into the text box for the data to be placed in the Excel worksheet. Finally after the data entry work is over, we can click on the command button ‘Click to Unload Form’ to close the user-form and remove it from memory.
Further Reading: