November 26, 2021

Using TextBox Change Event with Excel VBA

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:

Using TextBox Change Event to Enter Data into Excel Worksheet

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

Simple UserForm in Excel

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

Attaching Macro to Button Control on Excel Worksheet

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:

UserForm Object

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.