When creating an application, it is important to pay attention to a good UI (User Interface) and UX (User Experience). This article lists in no particular order several tips and points of interest that help improve the UI/UX of VBA userforms.
This article will be regularly supplemented with new tips and points of attention.
On a form, make sure all controls are neatly aligned. Use the same font everywhere and try to make sure that command buttons and such are the same size. If an application consists of several forms, make sure that all these forms have the same appearance. Always position the Cancel and Save buttons in the same place at the bottom of the form and make sure these buttons are in the same order on each form.
Startup position and scroll bars
When starting a form, make sure that it is in the correct position on the screen. This is almost always centered in the application screen. This can be set in the StartUpPosition property, but when using multiple screens this sometimes goes wrong, so that the form opens in a position where the user does not expect it or that the user first has to drag the form to the correct position.
It is also very important that large forms have scroll bars on smaller screens, because otherwise those forms cannot or can hardly be operated. You can set the scrollbars to always be visible, but that looks very ugly on larger screens.
Article center form and set scroll bars gives a method by which forms always open centered in the application screen and automatically add scrollbars in cases when needed.
It is obvious that a (short) text is chosen for the caption of the form that clearly shows what the form does. Often the same form is used to add new data and edit existing data. As caption is then often chosen for 'Add / edit of ...'. It is better to use VBA to adjust the captions based on the start mode during the start of the form in these cases. So with new data the caption becomes 'Add of ...' and with changes 'Edit of ...'. This way the user can always see from the caption in which mode the form was started.
Sometimes it is undesirable for a user to close a form with the close button. This is then often caught in the QueryClose event where a message box appears stating that the form may not be closed in this way. This is bad for the UX: a user first clicks on the cross and then has to click away a message box with a message that closes or this way is not allowed. In these cases it is better to completely hide the close button on the form. This article explains how to do that.
With a form it is good to make sure that it can also be operated as well as possible with a keyboard instead of with the mouse.
Set tab order
The tab order of a form must always be set correctly. That way, a user can navigate through the form in the correct order using the tab key. When you add a control to a form, the system automatically assigns a tab index number to this control. This is normally the next free number. But if this control needs to be placed somewhere between existing controls then the tab order will have to be adjusted. Otherwise, the use of the tab key will not work properly for the user, because the form will then be navigated in an illogical order. This leads to poor UX.
The tab order can be adjusted manually in a control by changing the number of the TabIndex in the properties window. This index starts with the number 0 and then increases by 1 each time. When a TabIndex of a control element is manually adjusted, the tab indexes of the other controls are automatically adjusted/moved up in the background so that no duplications occur.
Another way to properly set the tab order is to right-click on the form in design view. You can then choose tab order in a submenu, after which a form appears with the current tab order. This order can then be easily adjusted using this form.
A command button can be set to serve as a Cancel button by setting the value of Cancel to True in the properties window. Normally this is best set at the Cancel button. Pressing the ESC key on the keyboard will execute the command button code with Cancel set to True. This usually closes the form.
If none of the command buttons have a value of True for Cancel, nothing happens when the ESC key is pressed. Also nothing happens if the button with where Cancel is True is disabled or locked. If, when setting Cancel for a command button, it turns out that this is already set to True for another button, then this is automatically set to False for the other button. So there can be at most one button on a form with Cancel set to True.
For a command button, the Default property can also be set to True or False. If the Default property is set to True on a command button, it will automatically be set to False on other buttons. If a command button is set as Default, the code associated with this button is automatically executed with the Enter key on the keyboard, unless another command button has the focus.
For command buttons, a letter can be entered in Accelerator property. This letter will then be underlined in the command button caption. If a letter occurs more than once in the caption, only the first letter found will be underlined. With this accelerator, the command button can then be controlled with the keyboard by the key combination Alt + Accelerator. Make sure that a letter is chosen for the Accelerator that occurs in the caption of the button and make sure that on a form these Accelerators are unique. The application does not prevent the same Accelerator from appearing several times, but when operated with the keyboard, the code of 1 button is executed.
Questions / suggestions
Hopefully, this article helped you to improve the UI-UX of a VBA userfrom. If you have any questions about this topic or suggestions for improvement, please post a comment below.