| klste@hotmail.de |
External Excel files open and edit with multi-column databases from one mask. Excel VBA
Multi-column databases have an important advantage, they can contain more information.
This program creates and manages such databases in external Excel files.
Everything works simply, an Excel file is opened from the mask and in each worksheet of the opened Excel file you can enter data in 10001 rows and 121 column, namely from row 1 to row 10001, from column 1 to column 121. In row 1 the headings are entered. In column 1, the program creates the identification numbers, each number for one line only, each number that appears only once on the sheet.
The remaining 120 columns are divided into 10 groups, each group can be given a label and each column with a heading is assigned a data type: text, number, date or time. All this is done in the "Database Constructor" mode. This mode also determines whether files are entered from a text box or a combo box. A combo box can contain up to 12 values.
In the "Enter" mode, data is entered into the database while adhering to the data type assignment. Editing items are not displayed for columns without a heading.
In this program, a filter is always applied when searching. In the filter view, only the filtered values are displayed in the ID number combo box.
An image can be added to each identification number.
All data of each identification number can be printed out.
Step 1. Create, name and format an Excel file. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
The first step is to create an Excel file, name it and format it.
The Excel file created is called “Personnel Database”.
The first worksheet in the file is called “Production”.
On this sheet, use the keyboard shortcut CTRL + A to select everything. Choose Arial font and 12 point size. Highlight line 1 and make it bold.
Everything else is entered into the file from the input mask.
Step 2. Open an external Excel file. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
In the second step, an external Excel file is opened from the mask.
If you click the "Open" button, you can open the "Personnel Database" Excel file.
In the combo box “Sheet” that appears, select the “Production” worksheet. You can now see the yellow "D" button (Database Constructor) and the blue "E" button (Enter).
The database is ready for processing.
Step 3. Database Constructor. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
In the third step, the group is given a name, column headings are entered, data types are assigned to the columns and control elements are selected.
First, the yellow “D” button is pressed. A row of 10 yellow buttons will appear, each button for a group.
The first group is called "Personal Data (Group 1)".
The columns are given the following headings:
Personnel number
Gender
Birthday
First name
Surname
Title
Street
Postal code
Location
Marital status
Shift start
End of shift
The following data types are assigned to the columns:
Text
Text
Date
Text
Text
Text
Text
Text
Text
Text
Time
Time
In the column with the heading "Gender" all data is entered from a combo box, so a combo box with 2 values is created. These are the following values:
Woman
Mister
In the column with the heading "Marital status" all data are entered from a combo box, so a combo box with 4 values is created. These are the following values:
Single
Married
Divorced
Widowed
In the column with the heading "Start of shift" all data are entered from a combo box, so a combo box with 3 values is created. These are the following values:
06:00
14:00
22:00
In the column with the heading "End of shift" all data are entered from a combo box, so a combo box with 3 values is created. These are the following values:
06:00
14:00
22:00
Group 1 is ready.
Step 4 Enter and edit. Multi-column databases in Excel.
Working with this program can be divided into 8 steps.
In the fourth step, the data is entered into the database.
First, the blue “E” button is pressed. A row of 10 blue buttons will appear each button for a group.
When a button is clicked out of the 10 blue buttons, the clicked button turns red, the combo box "ID No." can be seen in the upper left corner and you can also see the frame with the controls of the selected group.
In the combo box "ID no." are the identification numbers of already created entries. If a new entry is created, this combo box must remain empty.
When a control (text box or combo box) is clicked, the cell in the spreadsheet for which the selected control is responsible is selected.
Button, which is responsible for entering, when creating new entries has the inscription "Enter", when an entry is changed it says "Change".
Entries are always made within the selected group.
The new identification number is created by the program. If all data in the selected record is deleted by typing in empty input fields, the identification number of that record is deleted and replaced with a dash "_".
Pressing the yellow button "D" switches to the database designer. You can switch back to entry and change by pressing the blue "E" button.
Step 5. Insert or remove image. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
The fifth step shows how to add or remove an image for each identification number.
If an identification number is selected, two buttons "Insert picture" and "Delete picture" are visible.
An image is added with the "Insert picture" button.
An image is removed with the "Delete picture" button.
Step 6. Create filtered database. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
In the sixth step, a copy of the database is created, the data in the copy is filtered and the filtered ID no. displayed in the identification number combo box.
The "Create new database copy" button creates a database copy from the active spreadsheet. Recreated copy is not filtered.
This is how a filter is used in the "Copy from database" spreadsheet: Click cell A1, select "Home", "Sort and Filter", "Filter" from the menu bar.
If the input mask is called up now and the "Filtered database" checkbox is clicked, only filtered identification numbers will appear in the combo box for identification numbers.
Changes to ID number data are made in the active table, not the database copy.
If checkbox "Database" is clicked, all identification numbers will be in the combo box for identification numbers.
Step 7. View Database Copy. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
In the seventh step, a copy of the database is called up.
The "Show database copy" button activates the "Copy of database" worksheet without changing anything there.
If the user needs it, he can change something in this sheet himself.
Step 8. Print data of selected identification number. Multi-column databases in Excel
Working with this program can be divided into 8 steps.
In the eighth step, the data of the selected identification number is made available in the spreadsheet for printing.
When an identification number is selected, the “Sheet” button is visible.
The "Sheet" button displays the data of the selected identification number in 6 columns on the "Sheet" worksheet.
Only data from columns with headings is transferred.
The picture is in cell A1.
Cell C1 contains the selected identification number.
The right header of this sheet contains the filename of the external Excel file, name of the active sheet, selected identification number and page number.
The opened external Excel file will be closed and saved when red "Mask" button is clicked.