FOUR ELMS BOOKKEEPING Mudford, Yeovil, Somerset  01935 850807

Examples of VBA UserForms

This page shows examples of UserForms created within some Visual Basic for Applications (VBA) projects by Four Elms Bookkeeping. This gives an idea of the kinds of graphical interfaces that can be created to facilitate Office routines. UserForms contain the familiar kinds of interactive objects you would find on any user interface today: checkboxes, radio buttons, pull-down menus, boxes for text input, buttons to click and labels with explanatory messages or images to facilitate data input or menu option selection by end users.

This first VBA UserForm example (right) contains radio buttons, textbox and password inputs, buttons to enter options and a checkbox to to allow easy input of database parameters and user options. To prevent user input error some of the user input features are disabled when certain objects are clicked as they become excuded options. Database parameters are checked on submission - if the program cannot find the database or if the password is wrong it returns to the UserForm and prompts the user to try again. Click on the image to enlarge it.

The next example (left) is a bank reconciliation VBA UserForm. Up to fifteen bank payments or receipts can be displayed per screen with checkboxes to reconcile any transaction. Reconciled transactions are coloured blue to differentiate them from unreconciled transactions, while payment and receipt amounts in the accounts are coloured differently. Errors in the company's own records can be entered as adjustments in accompanying textboxes. A bar at the bottom gives a running total of unreconciled and reconciled bank balances as payments or receipts are checked off while buttons at the bottom allow data validation and data entry. The data entry button is held in a disabled state until a successful data validation is achieved (to prevent data error) and is linked to code which posts relevant entries to accounts. Click on the image to enlarge it.

The third example (right) is a multipage VBA UserForm. Each of the three pages are accessed by clicking on the tab at the top. The pages contain different sections of a payroll entry for the user to review, with options to change the status of each section. In the page displayed there is a box for each week's pay. VBA code is linked to the UserForm which ensures that the number of boxes on display equals the number of weeks pay input by the user. The options available to the right pull-down menu (or "ComboBox") depend on the number of weeks pay being discharged this period. (Weeks paid in advance cannot exceed the total number of weeks being paid.) Characters entered by the user into the boxes are strictly controlled by the VBA code so that any non-numeric entries are instantly deleted and entries are automatically converted to pounds and pence format (two decimal places). To prevent accidental increase of the total pay by entries in the deductions box at the bottom right of the form linked VBA code places a minus sign in front of any positive entries. A variety of data validation code checking for common errors is integral to this UserForm. Click on the image to enlarge it.