Using VBA Forms to control Data Input

We will take an in-depth look at the different controls aswell as the problems developers commonly encounter along with theirlimitations. A summary is listed in the table below:

Can I add a dropdown list to a form in Visual Basic?

Yes, it’s called a combobox and it’s available in Visual Basic Editor’s toolbox. You can specify a RowSource within a combo box’s properties which can be a list of items from your Excel spreadsheet. You can also build a list within your code using AddItem. As with a dropdown list in an Excel worksheet, you can decide whether users should be able to enter additional items into your combobox. If you want to permit freeform entry then you should set MatchRequired to False.

When creating a combobox, you often have to decide on a method for ensuring the dropdown options remain up-to-date. If you set a row source equal to a range of cells on a worksheet, such as ‘=Sheet1!A1:A5, then the range will not dynamically update to include a 6th option. A better practice is to use an Excel table, assign a range name to the desired list of data, and set this as the RowSource. It is a good idea to confirm the RowSource for each combobox prior to showing your user form. This ensures the list is refreshed.

Combo box options can be populated when a form is loaded using AddItem, either as a semi-colon separated array or one at a time. Generally it is more user-friendly to let users edit dropdown lists in Excel not Visual Basic, especially because Excel tables expand as required and can easily be sorted alphabetically. The best case for AddItem is that the list has no reason to exist in the Excel worksheet. If you wanted to populate a continent combobox from a table containing continents and countries, you could loop through the table with VBA using AddItem to ensure each continent was only added once. Even then, copying the list of continents to an unused Excel worksheet, removing duplicates and assigning a range name would require less code and be easier to audit.

Once you have your dropdown, you have to decide whether to restrict entry using MatchRequired. Our experts prefer to set MatchRequired to False. Otherwise forms can throw up an ‘Invalid Property Value’ error if a user leaves a combobox having cleared it or typed something in error. You can still force a user to select from the dropdown list because the entire form environment is built around code. If the user is expected to confirm the form is complete before the data gets transferred to the spreadsheet, you can validate entries then. Alternatively, you can query the ListIndex property of a combobox as it is being changed. If a value of ‘-1’ is returned then you can write an If/Then statement to clear the combo box, or reset it to a preferred default.

When should I use a listbox instead of a combobox?

A listbox can be used to permanently display a list of items on-screen within a VBA form. This can give users the opportunity to review a list of items they have already selected and remove any they don’t want. With the MultiSelect property to either Multi or Extended, listboxes also let users select multiple options at the same time; something comboboxes do not allow. If a listbox can only be navigated by scrolling, then it is often best to use a combobox as these can be developed further to incorporate search functions and dynamic updates.

The unique property of a listbox is the ability to select multiple items. If you set MultiSelect to Multi, every item requires an individual click. Although Extended allows selection of blocks of items, the behaviours are not intuitive to an untrained user, so our consultants prefer sticking to Multi. If you want to give users a shortcut to select all items, it is better to facilitate this through a separate button that sets each list item’s selected status to true.

One of the challenges of letting users select multiple items in listboxes is that you may need to transfer the output in a single cell in an Excel table, or a single field in a SQL database. If you loop through all the items in the list, you can concatenate all the selected items into a single text string. In the past, this wasn’t searchable but Microsoft is currently adding the ability to create lists within single cells of an Excel spreadsheet and accurately filter them using the HASANY and HASALL functions. This will make listboxes more valuable.

The biggest problem with listboxes is that it is not practical to display a few hundred individual products on the screen at once. In those circumstances, a single listbox cannot show a user all the currently selected products on-screen at the same time. The workaround is to display the selected products in a second listbox. However, this means you are no longer getting any benefit from allowing multiple selections in the first listbox. Instead you are using screen space to display a list through which users have to scroll.

In the circumstances, it is better to let users find items through a smaller combobox, before viewing their selections in the listbox. This uses space more efficiently. If you set match required to false, you can dynamically filter the combobox list as a user types using a private change event. Excel4Business can help develop a process that lets a user select the items they want as quickly as possible.

When should I use a checkbox in a userform?

A checkbox lets users select yes or no to a simple question, confirm completion of part of a process, or to give consent. It works for the last two because, if someone has to check a box to continue, Excel can detect the box has been checked. The downside of using a checkbox to indicate yes or not is that it must have a default value. If a checkbox can be checked or unchecked in a completed form, it can be impossible to know if a user has meant to leave a box unchecked or has simply failed to answer the question. Option buttons are often better where yes and no are both valid responses.

At Excel4Business, we used a checkbox for one of our logistics clients who needed to confirm that products were available for shipping. This is a good use for a checkbox because the process requires the box to be flipped from its default ‘false’ value to its checked ‘true’ value. When a user clicked a checkbox, it could trigger further code through a Change event. In this case, it requested an initial and completed a date field to provide a n audit trail.

Another advantage of checking a box as part of a process is that you can prevent the checking of the box until other conditions are met. If you have to enter contact details before checking a box then the VBA userform can be designed to disable or hide the checkbox until it can be legitimately checked. Alternatively, you can conduct the checks when a user attempts to check the box and draw their attention to any missing details. Both methods are intuitive.

The use of a checkbox to answer yes/no to a question is best reserved for situations where the form is being completed by experienced users and the checked option is the exception rather than the rule. A good example would be a VBA form built for the use of estimators in a construction company where the users can be trusted to work through the form diligently. Using checkboxes for questions like ‘XYZ permit required?’ then makes perfect sense.

When should I use option buttons in a userform?

Option or radio buttons are the fastest way for users to answer multiple choice questions in VBA. They take more space on-screen than the alternative of a combo box so are best used on short forms where space is not a premium. They are also inferior to command buttons that feature embedded images when aesthetics are important.

A common use would be in a form containing a single question. If you put a Print button on a worksheet, you can give users the option to produce a hard copy output, an Excel workbook, or a PDF. You can either open the form with a preferred option selected and perform the operation when a user clicks OK, or you can use the selection as a VBA trigger to close the form and print the workbook as required. If you start to combine option buttons with other questions and form elements, you cannot use a button’s selection as a shortcut to closing the form.

A good use of option buttons in longer forms is to help users answer Yes/No questions. As ‘Yes’ and ‘No’ are very short, you can typically arrange two option buttons inline with one another in space that would otherwise be wasted. If the buttons have their default value set to false then, unlike with a checkbox, a user can be forced to decide between the two options.

This can create a complication. If you have several Yes/No option buttons in a single form, Excel needs to be told the responses to each Yes/No question should be treated independently. This can be done through the GroupName property. Each set of option buttons should be given its own identifying GroupName. Excel will then assume up to one button can be selected in each group, as opposed to a single button over the entire form.

How should I visually guide users through my VBA forms?

As with any form, there should be a logical flow as a user looks down the form. Having vertical lines run down the page removes visual distraction. Frames can be used to visually split the form into sections. Incomplete or required fields can be highlighted. To stop questions being completed out of sequence, you can disable or hide controls, labels and textboxes entirely. You can also prevent information overload by warning users only when they make a mistake, instead of overloading a form with content.

When designing a form, every aspect of layout needs considering. For example, imagine a box into which users should enter a currency amount. Firstly, do you need to display the box at all until a user enters the item that is being valued? Should the box then default to a standard price? Do you display cents or pennies in a separate entry box or let users enter the decimal point? Do you set the width of the box to fit a reasonable maximum value or just define a MaxLength in a textbox’s properties? If a user fails to enter a number, or includes a currency symbol, do you highlight the mistake automatically, do you correct it automatically or do you wait until they click OK at the end of the form?

That’s before considering the currency aspect. It makes sense to display the currency in the field label (if fixed), or for users to select the currency from a combobox (if variable). If they need to enter an exchange rate for foreign currencies, you have the option of keeping the exchange rate box hidden until it becomes relevant. As it makes sense to put a currency label immediately alongside a value, you may even want to request the exchange rate from users in a separate form before displaying it on the original form.

A common problem is ensuring users supply all the required information. Traditional web forms used red asterisks to guide users as to which fields were required. Although you can place an asterisk in a userform label, you cannot use different font colors within a single label. A more modern method would be to color the background of input boxes that need to be completed in a pale red. This can be done via the BackColor property and, once a valid entry has been made, you can change the background color to something neutral. This can be implemened via a VBA change event.

If you have several input boxes, it does not make sense to write near identical code in every control change event. Our consultants would either call subroutines from within each change event or use a class module. The best approach for efficiency and maintainability depends on the exact number of forms and different control types.

Should I change the content of my VBA form’s boxes dynamically?

If you want to help users search lists, or prevent them entering text in number fields, you can use VBA to trigger events when buttons are clicked or boxes changed. If you are considering writing such code, it is generally a sign the code would add value and, with AI assistance, it can be easier to write than in the past. The question is always whether it adds enough value to be worth the effort.

Excel4Business helped a dog walking business keep track of their bookings. The user wanted a simple calendar they could click that used their corporate palette. One requirement was that the selected date should be highlighted. In practice that meant the selection of any other date would have to remove the highlighting from the previously selected date. We created a calendar consisting of a series of buttons. Each button face would feature the day of the month so, whenever a new month was selected, the button faces needed rewriting.

In the example, there is a hierarchy of changes. Changing the month or year changes the grid of dates. Clicking the grid of dates resets the highlighting of the grid then highlights the selected date.

The danger when changing VBA forms dynamically is that you can end up in recursive loops that behave like circular references in formulas. The big problem is that there is no VBA form equivalent of Application.EnableEvents = False so every event can potentially trigger another event.

A common example mistake could occur when building a form in which clearing a product code clears a product name, and vice-versa. Obviously once the product code is cleared, it does not need clearing again because it has cleared the product name. Hoewver, if you do not write code that ignores boxes that are already clear, Excel will get stuck in a loop and crash.

As well as considering what may happen when your form is open, you should consider what code might run when you are preparing the user form. If your macro is written to display spreadsheet data in a form, you may trigger a series of unnecessary change events before the form is even displayed. You can avoid this by testing whether the form object is visible before running any events within the form.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed October 2026.