ActiveX controls – TextBox in Excel VBA

Building an application for entering data – How to insert a TextBox (ActiveX control)

The text field has already been introduced to the previous part of our course. The field is primarily for us to enter data and then to process them in the VB editor. Inserting a TextBox text field starts with selecting it in the Developer tab from among the ActiveX controls. Let us enter 4 text fields in succession above the columns login, name, surname and e-mail. The best way to make boxes the same size is to create one text field and then duplicate it. Let’s also create on our sheet one CommandButton command button named “Enter” and set it over the ID column. You can enter the name in the Caption field  by selecting the button’s properties in advance.

 

Building an application for entering data – Programming a textbox TextBox (ActiveX control)

Our application already has 4 text fields. We should program them now. Our goal is to write a program that, after entering data into fields, will enter them into the appropriate columns of our 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 database not to be repeated, so after entering an existing one in the login column the MsgBox window was displayed with the corresponding message. In addition, we will introduce a simple functionality so that the numbers of our records will always appear consecutively in the column. We create a command button on our sheet. First, double-click our “enter” button in design mode. We 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.

 

Create a growing ID and enter data from text fields

In order for each of our records to receive an ever higher number in the ID field, we should program this functionality accordingly. The easiest way is what is the highest value in column A.

Explanation of the above code:

  • We created 2 variables with the data type Single. The sngId variable is responsible for the consecutive numbers of our IDs. The variable sngWiersz will specify the line that will be currently filled with values
  • Using the Max () sheet function , first specify the highest value in column A and add 1 to it
  • Using the CountA (Count) sheet function, we count the filled rows in column A and add 6 to them to start entering data from row 7.
  • In the block responsible 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 our 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 simplifies searching for the first unfilled row – it assumes that the ID field can not 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 .

Entering unique values ​​in the login field

Our code in its current form assumes that logins can be repeated. Let’s modify it in the following way:

As we can see in the above example, we added another variable representing the counter (intLoginLicznik). The code checking whether the login is in the database at the beginning checks if the login was already entered. Using the loop, he 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 GoTo instruction, the  program sends us back to the very end of the code ( endlabel label ). The message about the existence of a login in our database is displayed with the vbCritical attribute. If the login is not in the database, our record is entered. To check the operation of the application, let’s introduce some examples to the text fields.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: