Building an application for entering data – How to insert VBA TextBox (ActiveX control)
VBA textbox has already been introduced to the previous part of VBA course. VBA textbox field is used to enter data and then to process them with VBA code. Inserting a TextBox field into Excel sheet starts with selecting it in the Developer tab from the ActiveX controls.
Now drop 4 textbox fields above columns login, first name, last name and e-mail. The best way to make boxes the same size is to create one text box and then copy and paste it. Let also create on your sheet one CommandButton named “Insert” and set it above the ID column. You can enter the name in the Caption field by selecting the button’s properties.
Building an application for entering data – Programming VBA TextBox (ActiveX control)
Application already has 4 VBA textbox fields. Our goal is to write a program that, after entering data into fields, will enter them into the appropriate columns of excel sheet. Importantly, we would like data to be entered into the next lines of our table without overwriting existing data. We would also like logins in our table not to be duplicated, so after entering an existing one in the login column the VBA MsgBox window will be displayed with the warning message.
In addition, we will introduce a simple functionality so ID numbers will always growing in the column. Create a command button in your sheet. First, double-click your “Insert” button in design mode. You can immediately enter the code that will start after a single button click. Note that we do not write code in the module and in the Data Sheet code at this time.
VBA Excel: Create growing ID and enter data from text fields
In order for each of your records to receive greater number in the ID field, you should write code for this functionality. The easiest way is checking the highest value in column A.
Private Sub CommandButton1_Click() Dim sngId As Single Dim sngRow As Single 'Selecting last unfilled row and unique ID creation sngId = 1 + Application.WorksheetFunction.Max(Range("A:A")) sngRow = 6 + Application.WorksheetFunction.CountA(Range("A:A")) 'data entry Cells(sngRow, 1) = sngId Cells(sngRow, 2) = LCase(TextBox1) Cells(sngRow, 3) = UCase(Left(TextBox2, 1)) & LCase(Mid(TextBox2, 2)) Cells(sngRow, 4) = StrConv(TextBox3, vbProperCase) Cells(sngRow, 5) = LCase(TextBox4) End Sub
- We’ve created 2 variables with the data type Single. The sngId variable is responsible for the numbers of IDs. The variable sngRow will select row that will be currently filled with values.
- Using the Max() sheet function is checking the highest value in column A and add 1 to it.
- Using the CountA () sheet function, we are counting the filled rows in column A and add 6 to them because we start entering data from row 7.
- In the block used for data entry, enter the appropriate ID number into the appropriate line.
- We assign values to the next cells from the appropriate text fields, using the functions according to the requirements of application. We write login with lowercase letters. First name and last name with a capital letter. In addition, the value contained in TextBox4 is converted into small letters.
- The program in its current form is searching for the first unfilled row – it assumes that the ID field can’t be empty. If you are interested in a more difficult construction, in which not all rows have to be filled, I refer to the article about finding the last non-empty row in Excel VBA .
Excel VBA TextBox: Entering unique values in the login field
Your code in its current form assumes that logins can be duplicated. Let’s modify it in the following way:
Private Sub CommandButton1_Click() Dim sngId As Single Dim sngRow As Single Dim sngLoginCounter As Single 'Selecting last unfilled row and unique ID creation sngId = 1 + Application.WorksheetFunction.Max(Range("A:A")) sngRow = 6 + Application.WorksheetFunction.CountA(Range("A:A")) 'login check sngLoginCounter = 7 Do While sngLoginCounter <= sngRow If Cells(sngLoginCounter, 2) = LCase(TextBox1) Then MsgBox "This login is already in use. Enter another", vbCritical GoTo endlabel End If sngLoginCounter = sngLoginCounter + 1 Loop 'data entry Cells(sngRow, 1) = sngId Cells(sngRow, 2) = LCase(TextBox1) Cells(sngRow, 3) = UCase(Left(TextBox2, 1)) & LCase(Mid(TextBox2, 2)) Cells(sngRow, 4) = StrConv(TextBox3, vbProperCase) Cells(sngRow, 5) = LCase(TextBox4) endlabel: End Sub
As you can see in the example, we’ve added another variable representing the counter (intLoginCounter). The code checking whether the login is in the database at the beginning checks if the login was already entered. Using the do while loop, it searches all data in column B and compares it with the value entered in TextBox1 starting with row 7.
If the login is in the database, using the VBA GoTo instruction, the program sends us back to the endlabel point. The message about the existence of a login in database is displayed with the vbCritical attribute. If the login is not in the database, your record is entered. To check the operation of the application, let’s introduce some examples to the text fields.