FOUR ELMS BOOKKEEPING

Mudford, Yeovil, Somerset
Telephone icon 01935 850807


Examples of VBA UserForms

This page shows examples of UserForms created by Four Elms Bookkeeping using Visual Basic for Applications programming language. UserForms contain checkboxes, radio buttons, pull-down menus, boxes for text input, buttons for executing commands together with explanatory messages and images to facilitate data input or choice selection by end users.

VBA UserForm for payroll: an example of a VBA UserForm created by Four Elms Bookkeeping.

The first VBA UserForm (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.

VBA UserForm for bank reconciliations: an example of a VBA UserForm created by Four Elms Bookkeeping.

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.

VBA Multipage UserForm for payroll calculations: an example of a VBA UserForm created by Four Elms Bookkeeping.

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.

Return to Excel macro development page

Four Elms Bookkeeping home page

If your organisation could benefit from customised Excel workbooks, Visual Basic macros or other coded calculation tools, please contact Four Elms Bookkeeping to discuss your requirements.

Proprietor: Richard Waggett B.Sc., Ph.D., MICB, CBDip.,Dip.PM


2012 Four Elms Bookkeeping