I’d like to describe about coding of userform in EXCEL VBA as user interface of database. You could set controls for entering data, selecting from choices, and coding procedure, etc. I would be happy with your help if you could understand the characteristics of controls and design a convenient and robust system.
- OptionButton, CheckBox, ListBox and ComboBox as a choice
- TextBox
- CommandButton
- Password
1. OptionButton, CheckBox, ListBox and ComboBox as a choice
1.1 Restriction which control should you select.
You might had to decide which control should you set on userform depending on the situation. First, number of choice is most important. Can you only choose one option? Or are you permitted multiple choices? Next, is there enough area to present the choices? Last, does user have to enter additional choice? Or does administrator have to define the choices?
1.2 Can you select only one? Or are you allowed to select multiple choice?
If choice was one, you could set OptionButtons in Frame or ComboBox. If you need multiple choices, you could set CheckBoxes in Frame or ListBox. I’d like to draw your attention here that multiple selection doesn’t mean unique value and doesn’t become candidate key to identify record in database. It isn’t first normal form because it is array rather than atomic value.
1.3 Enough area for setting control?
If you could have enough area on UserForm to present all choice, you would set OptionButtons or CheckBoxes in Frame. If you could not have enough area, you could set ComboBox or ListBox. Or you would have to adjust balance each other or expand UserForm area.
1.4 Is it possible that items may increase after system has been launched?
It’s a serious problem in management of database to force users to add items. It inserts items into transaction table, that isn’t exist in master table. It violates referential integrity constraints. I think that user should not be allowed to add items with ComboBox and new items should be added to master table on the other form.
| Control | Multiple choice | One choose | Area restriction | Required item added |
| OptionButton | ✔ | |||
| CheckBox | ✔ | |||
| ListBox | ✔ | ✔ | ✔ | ✔ |
| ComboBox | ✔ | ✔ | ✔ |
2. TextBox
You can enter various data into TextBox, so it’s difficult control to master. It’s typical value list as below.
- Last name and first name
- Birthday
- Gender
- Organization, department
- Zip code
- Address
- With or without of an attribute
These input values are classified as following data type:
2.1 String
Text data, for example, name of human, organization, department, zip code or address. Zip code should not be treated as numeric. Phone number should not be, so. Although these data have number, they should be treated as string and should be checked length of string or number of characters in validation.
2.2 Datetime
Date time data type, for example, birthday or entry date. Excel can treat date type after January 1, 1900. To tell the truth, Excel has bug in DATEDIFF function, it would not be so big problem for most purposes.
2.3 Numeric
Numeric required calculation such as finance or salary, measurement of experiment and physical quantity such as length or mass. For further classification, integer, decimal and currency. You would need to select appropriate decimal type depend on required accuracy.
2.4 Boolean
You would describe with boolean that you could tell with ‘yes’ or ‘no’, such as a disease status, smoking status or drinking status. But I think you should use Boolean not in TextBox but in CheckBox.
3. CommandButton
On CommandButton you could describe the procedure that is activated by click, such as selecting records from database, updating records in database, inserting records into database or deleting records from database.
- Select record
- Update record
- Insert record
- Validation
- Delete record
3.1 Select record
To select and present records from database, you would need to specify search criteria. With such criteria as name, birthday, age or gender entered into TextBox or ListBox, searching with Auto Filter or Find method and presenting result records, you set the code on CommandButton to activate by click. ListBox might be most appropriate control, because obtained records would be more than one. It’s available to use whole match, forward match, backward match and intermediate match in search method, you could choose depend on situation. An error would occur if you couldn’t get records, so you should catch error and display alerts.
3.2 Update record
To update records in database, the data obtained by described as above have to be replicated to such controls that you can edit as TextBox, OptionButton or ListBox. You would set CommandButton to replicate data. You need to set CommandButton, code the procedure writing back the data you had fixed, that is activated by click.
3.3 Insert record
When there isn’t search result, it means that you should insert record into database. When you enter items into controls, system would be required to verify data. When entered data have passed validation, they would be allowed to be registered. If they didn’t pass validation, system should display alert to user.
3.4 Validation
There are formal and semantic validation. Formal validation verifies whether there is control without input, whether data type is appropriate. Semantic validation verifies that magnitude relation of number or context of date are appropriate. If incorrect data have been inserted into database, it’s hard to find it. It’s standard rule to validate data on user form. If you need, you could use regular expression. It’s one of the most important process in order to design robust system.
How to execute formal validation of TextBox on user form with EXCEL VBA?
How to validate empty value in controls on user form of Excel VBA?
3.5 Delete record
It may be required to remove record from database in some situation. When you operate worksheet as database, the record identified by Auto Filter or Find method should be removed by Delete method. “Delete” button should be set far from other buttons because of fail safe and when it’s clicked, system should ask user whether user really wishes to delete the record by message box.
4. Password
It’s standard that system requires password when you open system as administrator. In order to enter password, you can use Input method or custom form. I’d like to recommend custom form.
Because Input method is easy to implement that Excel has already equipped function, you could trace user’s behavior with setting Variant type as return value. But it’s only and fatal defect that Input method couldn’t mask input value.
It’s true that custom form requires coding effort but it would be much more your benefit that you can mask password by modifying properties of TextBox.