Designing Complex Excel VBA UserForms for Data Entry
Although the technology is old, Excel forms are very powerful because they provide a tightly controlled environment in which users can make edits to a protected worksheet. A well laid out form can also display information in a more readable format than when the same data is written in an Excel table. Although clunky in appearance a VBA form can also be used as a starting point from which to create a web app using AI.
This article deals with some common questions such as how best to lay out a form and how to make the form flow by hiding or locking the various elements. We also look at when it makes more sense to validate data as a user types, or when the entire form has been completed. We only consider textboxes as dropdowns, listboxes and other self-validating controls are the subject of their own article here.
Our video guide below illustrates the basic components of a Visual Basic form. It shows how to add labels and freeform textboxes to a new form, and how you can validate data, before sending it back to the spreadsheet when a command button is clicked.
This article looks in more detail at problems that arise as forms and their user base expand. A summary of the topics covered is listed in the table below:
How can I make my VBA form visually appealing?
VBA forms are built for functionality so a visually appealing form tends to be clean without obvious visual distractions. Ensure different labels and fields line up in logical columns, and that any unused space remains towards the bottom right corner of the form.
There is frequently a trade-off between arranging fields in the most natural order, making the form visually appealing, easy to use, and using space efficiently. An attractive e-mail field should be wide enough to fully display 99% of e-mail addresses. It should be at least 25 characters in width, ideally 30. You would typically put an e-mail field next to a phone number which, even with spacers, will only be half that. The best compromise is generally to set both fields to the same width.
If you want users to enter a North American phone number, it is best to split the phone number into three separate boxes e.g. (123) 456-7890. This creates a problem if you want a user to enter the state and zip immediately above. You can make space for a state code dropdown above the ‘123’ box, but it’s very difficult to balance a ‘Zip’ label, a zip code field above the ‘456’ and ‘7890’ boxes. Options include putting the phone number in a separate column to the address, making the phone number freeform, looking up the state from the entered zip. Our experts can advise on the best approach taking into consideration the form’s user base.
One helpful way to break up a form is to use frames which are available in the Visual Basic toolbox. A frame can be given an appropriate title such as ‘Contact Details’ in the Caption field in Properties and it creates a box into which you can put relevant fields. By visually breaking the form into a series of smaller frames, the eye will get less distracted by design differences between sections of the form. The frames also help a user navigate the form. If you have more than 10 fields and do not wish to use frames, you should consider adding extra labels to serve as headings.
How do I ensure my VBA form fits on a user’s screen?
Form controls can be reduced in size and given scroll bars to help users navigate their contents. If there is too much information to display on-screen at once, you can add the MultiPage control to your form. This is similar to having a spreadsheet with multiple worksheets. If designing a form for personal use on a known machine, you can also reduce font sizes.
When designing a VBA form, it is good to be conservative in your use of screen space. Although Excel Visual Basic isn’t available on smartphones or most tablets, desktop users frequently arrange windows to reduce the size of the Excel app. VBA forms technically live outside the Excel app so will encroach on other apps but it does not provide a good user experience. The only exception would be if your solution uses VBA forms so extensively that it no longer feels like Microsoft Excel.
There are two main reasons developers build large forms. The first is a desire to display lots of different pieces of information. If a database has 50 editable columns, you will need 50 labels and 50 input boxes to edit them all. If the VBA form is being used as front end for a SQL database, it makes sense for users to make all the required edits before updating the record in SQL. Although this means all 50 fields have to appear in the same form, it does not mean they need to appear on-screen at the same time. Instead you can split them across different tabs in your form using the MultiPage control.
The other reason for a large form is because you want a single control to display a large amount of information. If you have a list of 20 items in a list box, it’s nice to let a user see all the items at once before checking off those required by having the MultiSelect option set to Multi. Sometimes the ‘nice to have’ isn’t practical and it makes sense to display fewer items on-screen. Excel will automatically add a scroll bar to a list box. If you want a user to see the items they’ve already selected, our consultants can build forms with two boxes. The first displays the items you might select, and the second displays the selected items. Items can be added to, and removed from, the second box in an intuitive way.
In Excel, a good way of displaying more information on-screen is to reduce font sizes because anyone who cannot read the smaller fonts has the option of zooming-in to view cells’ contents. There is no zoom option with a VBA form so you should only reduce the standard font sizes if the spreadsheet is for personal use.
How do I help users complete my VBA form in order?
In North America and Western Europe, users will expect to complete a form from top-to-bottom or left-to-right. If the completion order matters, you should organise controls in this manner even if it means compromising on aesthetics or space efficiency. You can add numbers to section labels or frames to make it even clearer. Some users may want to tab through the form. Every control has a TabIndex property. A user tabbing through the form will cycle through the fields in numerical order by TabIndex.
Sometimes users do not need to complete all fields in a form. If you’re maintaining a client database, you might wish to store the websites of business clients, but not regular consumers. The most user-friendly approach would be to hide the website field until a business name is entered. Our experts can write code that triggers whenever controls are changed. So, in this case, we would add e.g. a BusinessName_Change event that set the website field’s Visible property to True whenever a business name was entered, and set it to False whenever the BusinessName field was cleared. To keep things tidy, it would need to show or hide the accompanying website label at the same time.
Instead of hiding controls completely, you can also disable a control which has the effect of greying it out to users. To disable a control, you should set the Enabled property to False. A good use for this would be a date field that you expect a user to complete through a pop-up calendar. Microsoft no longer provide a calendar control for Visual Basic but users can still select a date through a custom form that mimics a calendar. Disabling a date field can push users towards an adjacent button that brings up the custom form. The fact it’s still visible means users can see the date they’re responsible for editing.
Another reason for disabling fields is if the form is designed to help users view data as well as editing data. The same platform can be used in either instance. This can occur because different users have different roles, or because older entries get archived.
How do I validate freeform text entries in my VBA form?
Changing a textbox control can trigger a Visual Basic Change event that runs a macro. The code can check the entry against requirements and display an error message or correct the entry. An alternative is to validate entries when a user exits a control using the Exit event. This is essential if a half-completed entry cannot be validated. You can also validate entries when a user clicks a Submit button using the button’s Click event. If a field starts blank but cannot be blank in a submitted form, the error can only be caught at the time of submission.
The method for validating an entry depends on what would constitute an acceptable entry. Unlike in Excel, there is no native method to force users to enter numbers in a VBA form control. It’s possible to test whether an entry contains a number using the IsNumeric function. To stop users wasting time typing a sentence in a box that contains numbers, it makes sense to run a Change event every time a new character gets entered in the control.
If a user is expected to enter text into a cell, you can often prevent invalid data being entered. You can force users to select from a list by using combo boxes (effectively dropdown lists) with an appropriate RowSource. By entering a character limit in the MaxLength property, you can prevent users making entries that are too long. The disadvantage of the MaxLength property is that it doesn’t tell a user they’ve hit a character limit so adding a Change event gives you chance to tell a user why their input is being rejected.
Sometimes the validation logic will be more sophisticated. If a valid e-mail address must include a recognised domain, you cannot use a combobox without splitting the address into three; a textbox for a username, an @ label, and a combobox to select the domain. If you would prefer a single textbox, you can only validate the address once a user has typed it in full. This requires later validation.
Should I unload and load forms prior to each use?
Assuming you do not want a VBA form to display its last entered values each time it is opened, a form needs to be closed and reset between each use. You can either create a new instance of the form each time your code runs, or you can actively unload the form between each use. If you unload a form, you will need to load it again before the next use. Excel will load a closed form automatically when you run the VBA Show command. However, this prevents you making any changes to the form before the user interacts with it.
If a form is being used to interact with a table of data then it makes sense to reset your form prior to each use. In theory your code should populate every field from your table of data so any previous values would get overwritten. In practice, your code may end up structured such that writing to fields programmatically may not trigger the same form events as a user completing fields manually. If the form’s initial state is always the same default, it is easier to manage the process than if the last closed state could have a random variety of fields hidden and disabled. Unless the form is always being used to interact with the previously edited data, it is simplest to start with a fresh form each time.
If you plan to unload a form prior to each use, the most obvious structure for your code would be Load MyForm->make any required changes->MyForm.Show->MyForm.Hide (when a user is done)->any code that relies on the form’s data->Unload MyForm. As long as the Unload code always runs, that will be adequate. However, it does require considerable care to ensure the Unload step won’t get bypassed by error handling or different routes through the file.
An alternative approach is to assume a previous instance of the form is loaded every time a user wants to interact with it. If you enter Unload MyForm immediately before Load MyForm instead of at the end of your macro, the form will be reset for each use. You can also explicitly create a new instance of the form at the start of your code so you don’t need to worry about previous instances. Both these approaches address the same problem and, as VBA forms were built for an era of far slower computers, they will not cause any file performance issues.
If you want a VBA form to retain information from one use to the next, you should steer clear of loading and unloading forms. Instead, after an initial load, you can use the lines MyForm.Show and MyForm.Hide. These toggle whether the form is displayed on-screen. Note that, by default, any user clicking the red X to exit a form will unload the form in the process so that needs separate handling.
How do I treat form closures as a separate event?
A form can be closed by a user clicking the red cross in the corner, or it can be closed when a user taking some other action to close or hide the form. The most common action would be when a user clicks an OK or Cancel button but, in a form containing a single control, it could include a user choosing an option from a dropdown list. Whenever a user interacts to Change or Click a form control, a Sub can be triggered based on the nature of the interaction. Any code required can be entered after the code instructing the form’s closure. If the user uses the red X, you can use the QueryClose event.
In the situation that a user clicks an OK or Cancel button, the OK_Click event triggers a different Sub to the Cancel_Click event. A typical OK_Click event would validate any edits to the form, make any required changes to the underlying Excel file and hide or close the form. A typical Cancel_Click event would simply hide or close the form. Absent a Query_Close event, no code runs when a user clicks the red X so it will tend to resemble a user clicking Cancel.
Although the above works very neatly, it may not be the best from a development perspective. If you want the option of testing your code line-by-line, you cannot click through your Excel file while a VBA form is open. There are also circumstances where it makes sense to extract data from the form in routines that live outside the form itself. If so, you need to pass information on how the form was closed between different sub-routines. This can be done through a hidden field on the form itself.
The above code assumes that hitting the red X to close the form should just get rid of it. You can use the event MyForm_QueryClose(Cancel As Integer, CloseMode As Integer) to do something different. By setting cancel to true, you can prevent the form closing. You could ensure no data gets lost from the form by subsequently writing MyForm.Hide. You can also prevent users closing the form entirely which is helpful if you want users to agree to terms and conditions when using an Excel file.
All methods for closing forms involve users clicking somewhere on the form so they can be used to trigger events.
By Ed Bolton, founder of Excel4Business Ltd. Last reviewed November 2026.