If you want to prevent users to make 'mistakes' you can offer them ranges of valid choices.
A long as they make a choice from the options you offer they can be sure they make a valid choice.
I prefer that method to bothering users with messageboxes afterwards that they did something 'wrong'
A very useful tool for supporting users' choices is the combobox.
A combobox can reside in a userform or in a worksheet.
I will restrict the illustration to comboboxes in a userform.
Basically there a 2 kinds of comboboxes:
- a combobox in which the user can add new choices
- a combobox with a limited set of choices from which the user can only select one or none.
The most error proof combobox is the 'dropdownlist' combobox.
If you add a combobox to a userform or sheet it's default is the combobox in which the user can add options.
In VBA combobox1.Style=fmstyleDropDownCombo or combobox1.Style=1
If you prefer a 'dropdownlist' combobox you can change the combobox's behaviour by changing it's property 'style' to fmStyledropdownlist.
In VBA combobox1.Style=fmstyleDropDownList or combobox1.Style=2
In Design Mode you can adapt the default property for a combobox.
When loading the userform the combobox will now be a dropdownlist automatically.
It saves you coding and memory use during execution to set this property in DesignMode..
Populating the combobox
The easiest way to populate a combobox is to use it's property .List
You can populate a combobox by assigning a one-dimensional or multidimensional array to it.
An array can consist of:
- single characters
You can use Excel's builtin arrays (customlists) or produce your own.
A very handy tool to design an array is the method 'Evaluate'.
You can consider it to be the VBA equivalent of Excels' array formulae.
In can be written as Evaluate("...") or in shorthand, using vertical brackets: [....]
In the attachment only the shorthand method is being employed.
In the attachment you will find VBA code to populate a combobox, using only a oneliner at a time.
The data to populate the comboboxes with will only be produced using VBA. So no data in the workbook, nor elsewhere will be read and used.
The attachment contains comboboxes, populated with:
- positive numbers
- negative numbers
- lowercase characters
- uppercase characters
- textstrings containing a variable substring
- textstrings containing numbers
- years in the future
- years in the past
- monthnames (short & full)
- weekdaynames (short & full)
- dates in the future
- dates in the past
- dates in a specified future