Last Updated on January 11, 2023 by token
Excel VBA: Building an application for entering data
Let’s do an example that uses all of the known ActiveX controls. Your purpose will be to build a short application for entering client 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: Requirements of the data entry application
When building applications, you should have to write basic requirements under which it will work. You 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 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 fields.
- The value entered in the “login” column can’t be repeated. When you enter a duplicate login, the application should display a 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 with the ComboBox control. You can 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 using to delete data from the form.
VBA Excel: 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. It will improve the viewability of our application. In the View tab, also block windows from 1 to 6. Go to View tab > Block windows. Select rows 1 to 7 and click “Block windows”. Now you can scroll your sheet with always visible lines from 1 to 6. In these lines you will embed your form.
Starting with row 6, let’s create a table and name the columns as described in the following screenshot. Then it 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.
