ActiveX controls – Building data entry application in Excel VBA

Excel VBA training: Building an application for entering data

Let’s do an example that uses all of the known ActiveX controls. Our goal will be to build a short application for entering personal data. The application will be based on an Excel spreadsheet. Its main purpose is to simplify data entry into columns and enforce the correctness of entered data.

Excel VBA training: Requirements of the data entry application

Like every application, ours should have written down basic requirements under which it will work. We can define them in the following way:

  • The application is intended for entering personal data into an Excel spreadsheet
  • The data in the columns to be filled in is the ID, login, first name, last name, e-mail address, region, date of birth, type of contact, gender, marketing consent, date of addition
  • The data should be entered in the appropriate fields after pressing the “Enter” button
  • The field ID should be topped up automatically and always take the value by 1 from the preceding value
  • Data such as login, first name, last name, e-mail should be entered in the TextBox text field
  • The value entered in the “login” column can not be repeated. When you enter a duplicate login, the application should display a message informing you that such a login is already in our database
  • The application after entering the login should exchange text values ​​into small letters
  • After entering the first and last names, the first characters should be capitalized
  • The region and date of birth should be entered in the form of the Kombi (ComboBox) field. You must create separate combo boxes for the day, month and year
  • The “Contact type” column should be completed from the list box, and thus the ListBox element. It is possible to select one option at a time
  • The gender should be completed in the form of OptionButton buttons
  • The “Marketing Consent” field should be completed in the form of a CheckBox element and accept values ​​yes or no
  • After entering each of the records, the current date should be entered in the last column
  • The form should also contain a second button called “Clear” to delete data from the form

VBA Excel training: Building an application for entering data – creating a table

At the beginning, let’s create a new Excel file for this purpose. Let’s call it “Introducing personal_data.xlsm” and save it as an Excel workbook with macro support . In Sheet 1, let’s color lines from 1 to 6 in gray, this will improve the aesthetics of our application. In the View tab, also block windows from 1 to 6. View tab> Block windows. Select rows 1 to 7 and click “Block windows”. Now we can scroll our sheet with always visible lines from 1 to 6. It is in these lines that we will embed our form. Starting with row 6, let’s create a table and name the columns as described in the following screenshot. Then they should be: ID, login, first name, last name, e-mail, region, date of birth, type of contact, gender, marketing consent, date of addition. Set the width of all columns to 21 pixels.

Leave a comment

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

%d bloggers like this: