The UserForms in VBA are used to take input from the user and sometimes to display outputs and messages. Learn about the basics of userform here.
The userforms can have multiple and different components for the inputs. These components are called Controls of UserForm.
There are several types of userform controls, each having different properties and used for different reasons. It is useful to know how and when to use each type of the userform control.
In this article, we will learn how to use UserForm Control in Excel VBA.
Types of The UserForms Controls
There are too many userform controls in VBA. If we cover themIn this article, we will focus on the major form controls only.
1: Labels
2: TextBox
3: ComboBox
4: ListBox
5: CheckBox
6: OptionButton
7: ToggleButton
8: CommandButton
9: Frames
Let's have a look at all of these controls.
Labels:
The labels are used to simply show text labels. Like a label for an input area, a warning, a direction, etc.
To add a label to userform, simply select the Label option (A icon) from ToolBox. The cursor will turn into a plus sign. Drag to the area where you want to put the labels.
TextBox:
A text box is a basic open end input option from the user. The user can simply type in the inputs he wants to give. Basic scenario is Name inputs.
If you want to show some message when mouse hovers over the textbox, write it in ControlTipText in the property window.
ComboBox:
The comboxes are used to show a list of items when clicked. The user can select any of the displayed items from that list easily or type the input by themselves.
We use ComboBox in Excel VBA when we have an optional list for the user to select from. Users can select from the list or type their answer manually.
Select the combobox from the toolbox and drag on the form. It will insert a combo box immediately on the form. The combox's default name will be Combobox1, 2, 3, etc. You can change it from the property box.
How to Insert Items in ComboBox?
So now you have added the combobox to our form. But when we load the userform it shows nothing in the combo box. This is because we have not added any items to the combobox yet.
In this example, I want to add items to the combobox when the userform initializes (loads). So, we will use the userform even "Initialize".
Right click on the userform (not on any component) to select the whole userform. Select view code.
From events
, select initialize.
Now use the property AddItem of ComboBox Class and add as many items you want.
Private Sub UserForm_Initialize() ComboBox1.AddItem "2016" ComboBox1.AddItem "2017" ComboBox1.AddItem "2018" ComboBox1.AddItem "2019" ComboBox1.AddItem "2020" End Sub
Since we are adding the items in UserForm_Initialize() event, all items will be added to the combobox when the user form loads.
Retrieving the selected item from the ComboBox
To use the selected item from the combobox we simply use the Value Property of ComboBox.
Private Sub CommandButton1_Click() MsgBox ComboBox1.Value End Sub
Here we are showing the value we have chosen when the user clicks on the command button (we will get to the command button click event below).
Listbox
A ListBox is used when we want the user to select from a defined list. Sounds similar to ComboBox? Yes, a listbox is very much similar to the ComboBox except that the user can not type their answer in the listbox. They have to select from the available options only.
To insert a listbox in the form, select it from the tool box and drog on the form area.
The default name of the listbox will be genetically listbox1, 2, 3, etc. You can change it from the property box.
Just like the ComboBox, the VBA listbox is initially blank. To initialize it, we simply use the add property of the listbox class.
How to add items to the listbox?
As we added the items to the list of combobox in the initialize events of the form, we will add the items of listbox in the same event. Just edit the above initialize event to add items when the userform loads.
Private Sub UserForm_Initialize() ComboBox1.AddItem "2016" ComboBox1.AddItem "2017" ComboBox1.AddItem "2018" ComboBox1.AddItem "2019" ComboBox1.AddItem "2020" 'Adding Item to List ListBox1.AddItem "Jan" ListBox1.AddItem "Feb" ListBox1.AddItem "Mar" ListBox1.AddItem "Apr" ListBox1.AddItem "May" ListBox1.AddItem "Jun" ListBox1.AddItem "Jul" ListBox1.AddItem "Aug" ListBox1.AddItem "Sep" ListBox1.AddItem "Oct" ListBox1.AddItem "Nov" ListBox1.AddItem "Dec" End Sub
The above code will add names of the months to the listbox as soon as we load the form.
Retrieving selected item from the ListBox
To retrieve the item user has selected in the ListBox we use the Value property of the listbox object.
Here we add the month selected by the user to the input given in combobox.
Private Sub CommandButton1_Click() MsgBox ComboBox1.Value & "-" & ListBox1.Value End Sub
When we hit the command button, we get the selected items shown to us in the msgbox.
CheckBox VBA Control
The CheckBoxes are the binary controls that can be set to True or False. You can have multiple checkboxes and select more than one checkboxes at one time. This is basically used when a user needs to be able to select more than one option. Like multiple choice questions.
To add checkboxes to the userform, just select it from the tool box and drag on the VBA userform area.
When you add the checkboxes to the userform, the default name of the checkboxes are like checkbox1, checkbox2, etc. You can change the name of the checkboxes from the property box.
How to use the checkboxes on userform?
As soon as you add the checkboxes, they get clickable. Users can check or uncheck them. But, how do we know which checkbox is selected and which is not?
As I said, the checkboxes are boolean. So, if a checkbox is selected, it's value is True, otherwise its value is False.
The code below checks which checkboxes are selected and shows it in the Message box.
Private Sub CommandButton1_Click() Dim msg As String If CheckBox1.Value = True Then msg = msg & "CheckBox1 is selected" End If If CheckBox2.Value = True Then msg = msg & vbCrLf & "CheckBox2 is selected" End If MsgBox msg End Sub
If you want the user to be able to select only one option on the userform then use radio buttons.
OptionButton VBA Control
The radio/option buttons are used in the VBA userform when we want the user to select only one option from the available options. Like when forms ask gender, prefered language, age group, etc.
The radio button can be easily added to the userform by selecting it from tools and dragging on the form area. Drag and drop radio buttons multiple times for multiple radio buttons.
How to get selection of the radio/Option button?
The radio buttons are Binary. Their value can either be True or False. The selected radio button will be set to True and all others will turn to false. Only one radio button at a time will be true in a group.
The code below simply checks which radio button is selected and shows the message accordingly.
Private Sub CommandButton1_Click() Dim gender As String If OptionButton1.Value = True Then gender = "Male" ElseIf OptionButton2.Value = True Then gender = "female" Else gender = "other" End If MsgBox gender End Sub
Note that only one option button can be selected on the user form in the above example. But what if you want to have multiple groups of option buttons. In that case, we use frames. And that brings us to…
Frames in VBA UserForm
The frames are used to group to userform objects, like checkboxes and option buttons.
To use frames, first drag and drop that frame on the form area and then drop the other objects in that frame.
You can change the name of the frame from the caption in the property box.
All events are available for frames too as other userform objects. But preferably we just use them to group objects. You can set onclick events on fram to load some basic information for the objects in it, when it is clicked.
Toggle Buttons in VBA UserForm
The toggle buttons are also binary objects that can be set to on and off. You can use the toggle buttons to toggle something on and off.
To add a toggle button to the userform, simply select it from the tools and drag on the form area.
The below code is set on the onclick event of the toggle button. It changes the color of cell A1 to blue when the toggle button is pressed and resets it when toggle button is depressed.
Private Sub ToggleButton1_Click()
If ToggleButton1.Value Then
Range("A1").Interior.ColorIndex = 32
Else
Range("A1").Interior.ColorIndex = xlColorIndexNone
End If
End Sub
Command Buttons in VBA UserForm
These are the buttons that we use to execute the main VBA code like validations, calculations, form submissions, etc.
In the above examples, I have used the command button to submit the form. You can have as many command buttons on the userform as you want.
To execute codes when the button is clicked, just double click on the button. It will open the CommandButton_Click event. Write code here to execute on click of the command button.
The below code will run when you click the done command button and it will also close the userform.
Private Sub CommandButton1_Click() Dim gender As String If OptionButton1.Value = True Then gender = "Male" ElseIf OptionButton2.Value = True Then gender = "female" Else gender = "other" End If MsgBox gender Unload UserForm1 'closes userform End Sub
So yeah guys, these are the basic userform controls in VBA. I hope it was helpful. If you have any doubts regarding the userform controls and userform events, write it down in the comments section below.
Related Articles
Getting Started With Excel VBA UserForms | The Excel UserForms are useful for getting information from the user. Here is how you should start with VBA userforms
Change the value/content of several UserForm-controls using VBA in Excel |
To change the content of the userform controls use this simple VBA snippet.
Prevent a userform from closing when the user clicks the x-button by using VBA in Excel | To prevent the userform from closing when the user clicks on the x button of the form we use UserForm_QueryClose event.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
