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 the 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 in 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 designed for entering personal data into an Excel spreadsheet.
- The data in the columns need 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, insert date.
- The data should be entered in the appropriate fields after pressing the “Enter” button.
- The field ID should be generate automatically and always take the value max + 1.
- 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’t be repeated. When you enter a duplicate login, the application should display message informing you that this login is already in database.
- The application after entering the login should change 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 ComboBox control. You must create separate combo boxes for the day, month and year.
- The “Contact type” column should be inserted from the list box. It is possible to select only one position.
- The gender should be completed in the form of OptionButton.
- The Marketing Consent field should be completed using 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” which will be used 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. Let’s call it “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, insert date. Set the width of all columns to 21 pixels.