Database Range
Database Listobject
Userform as database entryform

1 Userform & Database

A userform can help a user to add, adapt or delete data in a database.
So you can help the user to prevent making mistakes or ruin the database.

This page contains 2 sample files.
In Excel 2007 the 'intelligent table' (in VBA 'Listobject') has been introduced.
In the first sample file no such Listobject is present, the second file illustrates the combination of a Userform and a database stored as a Listobject.

The sample files contain a minimum of code to illustrate the code othe matter.
The database is in the hidden sheet 'Data', starting in cell A1.
The first row contains the 'fieldnames'.
The user can perform all kinds of actions in the database: adding, adapting or deleting records.
The results wil be stored in the combobox of the userform.
The database itself will not be affected by the user's actions.
Only if the user clicks the commandbutton with the 'Save' icon, the database in sheet 'data' will be synchronised.

Purpose of this example:

- to illustrate how reading/writing in a workbook can be reduced to a minimum.
- the use of a combobox as a 'mirror' of the database
- the use of a combobox as a dropdownlist (.style =2)
- the populating of a combobox with a multidimensional array in only 1 VBA command: .List
- how to pass data using certain object properties.

2 Userform


- 1 combobox to store the database and to select a record (c_01)
- 6 labels with the column names (fieldnames) of the database (L_01 to L_06)
- 6 textboxes containing the data of each field in the active record; the user can adapt the data here (T_00 to T_05)
- 1 button to add a new record (B_01)
- 1 button to remove the active record (B_02)
- 1 button to store the adapted data into the database in the workbook (B_03)

When the userform is being loaded:
- read the database and populate the combobox c_01 with it
- load the fieldnames in the Labels L_01 to L_06 using their property 'Caption'

3 Adapt a record

- select a record in the combobox
- adapt one or more data in the textboxes
- every change in one of the Texboxes will be reflected immediately in the values in the combobox c_01, which is a 'mirror' of the database.

4 Add a record

- click the + button B_01 in the userform
- fill the data in the textboxes

5 Delete a record

- select the record in the combobox
- click the button with the red 'X'
- the record is being removed from the combobox c_01
- the first item in the combobox will be shown
- only if the user clicks the 'Save' button the record will be removed permanently from the database

6 Save the changes

- the code empties the database, except the first row, containing fieldnames.
- the values in the combobox ( = 'mirror' database) will be written into the database in the workbook.
- the userform will be set to 'hidden'; if you show it again you can continue the work in the database where you left it.

7 Close the userform without saving any changes

- If the user closes the Userform using the cross in the right upper corner all adaptations to that moment will be lost.