Toolbox Controls in Excel VBA
Learn how to use UserForm Toolbox controls, configure their properties, respond to events, collect user input, and build interactive Excel VBA applications.
1. What Is the Toolbox in VBA?
The Toolbox in the Visual Basic for Applications editor contains controls that you can place on a UserForm. These controls help you create an interface for entering information, selecting options, displaying messages, and executing VBA procedures.
For example, a student registration form may contain TextBoxes for the student's name, a ComboBox for selecting a course, OptionButtons for choosing a batch, and a CommandButton for saving the details.
2. How to Open the Toolbox
- Open Microsoft Excel.
- Press
Alt + F11to open the VBA editor. - Select your VBA project in Project Explorer.
- Choose Insert → UserForm.
- Click the UserForm designer.
- Choose View → Toolbox if the Toolbox is not visible.
- Select a control and draw it on the UserForm.
3. Common Toolbox Controls
| Control | Purpose | Example |
|---|---|---|
| Label | Displays descriptive text. | Employee Name |
| TextBox | Accepts user input. | Enter a name or salary. |
| CommandButton | Executes code when clicked. | Save or Submit |
| ComboBox | Provides a drop-down list. | Select a department. |
| ListBox | Displays selectable items. | Choose a student. |
| CheckBox | Represents a True/False option. | Currently employed. |
| OptionButton | Selects one option from a group. | Full-time or Part-time. |
| Frame | Groups related controls. | Personal details. |
| ToggleButton | Switches between pressed and unpressed states. | Enable a feature. |
| MultiPage | Organizes controls into multiple pages. | Personal and academic details. |
| SpinButton | Adjusts a numeric value. | Select quantity. |
| ScrollBar | Adjusts a value across a range. | Select a percentage. |
| Image | Displays an image on the form. | Company logo. |
| TabStrip | Provides tab-based navigation. | Switch between categories. |
4. Important Properties of Toolbox Controls
Every control has properties that determine its name, appearance, position, and behavior. Select a control in the designer to edit these properties.
| Property | Purpose | Example |
|---|---|---|
| Name | Identifies the control in VBA. | txtName |
| Caption | Sets displayed text on applicable controls. | cmdSave.Caption |
| Value | Gets or sets a control's value. | txtName.Value |
| Enabled | Enables or disables user interaction. | cmdSave.Enabled |
| Visible | Shows or hides a control. | lblMessage.Visible |
| BackColor | Sets the background color. | RGB(240, 248, 255) |
| ForeColor | Sets the text color. | vbBlue |
| Font | Controls font appearance. | txtName.Font.Bold |
| ControlTipText | Displays a tooltip. | txtName.ControlTipText |
| TabIndex | Helps define keyboard navigation order. | txtName.TabIndex |
Example 1: Configure controls
Private Sub UserForm_Initialize()
Me.Caption = "Student Registration"
With Me.lblName
.Caption = "Student Name"
.Font.Bold = True
End With
With Me.txtName
.Value = vbNullString
.ControlTipText = "Enter the student's full name"
End With
With Me.cmdSave
.Caption = "Save Student"
.Enabled = True
End With
End Sub
Create the controls named lblName,
txtName, and cmdSave before
running this code.
5. Label Control
A Label displays text such as field names, instructions, headings, and status messages. Users generally cannot edit a Label's caption directly while the form is running.
Example 2: Change a Label caption
Private Sub cmdWelcome_Click()
Me.lblMessage.Caption = "Welcome to Excel VBA!"
End Sub
Add a Label named lblMessage and a CommandButton
named cmdWelcome. Clicking the button changes
the displayed message.
6. TextBox Control
A TextBox accepts user input. It can be used for names, addresses, numeric values, and other data that needs to be processed by VBA.
Example 3: Read a TextBox value
Private Sub cmdDisplay_Click()
Dim studentName As String
studentName = Trim$(Me.txtName.Value)
If Len(studentName) = 0 Then
MsgBox "Please enter the student name.", vbExclamation
Me.txtName.SetFocus
Exit Sub
End If
MsgBox "Student Name: " & studentName, vbInformation
End Sub
This example checks for empty input before displaying the entered name.
7. CommandButton Control
A CommandButton executes an event procedure when clicked. It is commonly used for saving data, clearing fields, calculating values, searching records, and closing forms.
Example 4: Button click event
Private Sub cmdCalculate_Click()
Dim number1 As Double
Dim number2 As Double
If Not IsNumeric(Me.txtNumber1.Value) Or _
Not IsNumeric(Me.txtNumber2.Value) Then
MsgBox "Enter valid numeric values.", vbExclamation
Exit Sub
End If
number1 = CDbl(Me.txtNumber1.Value)
number2 = CDbl(Me.txtNumber2.Value)
Me.lblResult.Caption = "Total: " & (number1 + number2)
End Sub
Add two TextBoxes named txtNumber1 and
txtNumber2, a Label named lblResult,
and a CommandButton named cmdCalculate.
8. ComboBox Control
A ComboBox displays a drop-down list of values. It is useful when users must select from predefined items such as departments, courses, cities, or designations.
Example 5: Populate a ComboBox
Private Sub UserForm_Initialize()
With Me.cmbCourse
.Clear
.AddItem "Core Java"
.AddItem "Python"
.AddItem "C#"
.AddItem "Advanced Excel"
.Style = fmStyleDropDownList
.ListIndex = -1
End With
End Sub
Example 6: Read the selected course
Private Sub cmdShowCourse_Click()
If Me.cmbCourse.ListIndex = -1 Then
MsgBox "Please select a course.", vbExclamation
Exit Sub
End If
MsgBox "Selected Course: " & Me.cmbCourse.Value, vbInformation
End Sub
The first procedure populates the ComboBox, while the second checks whether the user has selected a course before reading it.
9. ListBox Control
A ListBox displays several items at once. It can support
single or multiple selection depending on its
MultiSelect property.
Example 7: Populate a ListBox
Private Sub UserForm_Initialize()
With Me.lstStudents
.Clear
.AddItem "Amit"
.AddItem "Neha"
.AddItem "Rahul"
.AddItem "Priya"
.MultiSelect = fmMultiSelectSingle
End With
End Sub
Example 8: Display the selected student
Private Sub cmdShowStudent_Click()
If Me.lstStudents.ListIndex = -1 Then
MsgBox "Please select a student.", vbExclamation
Exit Sub
End If
MsgBox "Selected Student: " & _
Me.lstStudents.Value, vbInformation
End Sub
Selected(index) property instead of relying
only on the single selected value.
10. CheckBox Control
A CheckBox represents a True/False choice. It is useful for optional features, employment status, report settings, and other independent selections.
Example 9: Read the CheckBox state
Private Sub cmdCheckStatus_Click()
If Me.chkActive.Value = True Then
MsgBox "The student is active.", vbInformation
Else
MsgBox "The student is inactive.", vbInformation
End If
End Sub
Add a CheckBox named chkActive and a
CommandButton named cmdCheckStatus.
11. OptionButton Control
OptionButtons allow the user to choose one option from a related group. They are useful for selecting a batch, gender category where appropriate, payment method, or employment type.
Example 10: Read an OptionButton selection
Create two OptionButtons named optMorning and
optEvening. Place them inside the same Frame
if you want them to behave as a single group.
Private Sub cmdShowBatch_Click()
If Me.optMorning.Value = True Then
MsgBox "Morning batch selected.", vbInformation
ElseIf Me.optEvening.Value = True Then
MsgBox "Evening batch selected.", vbInformation
Else
MsgBox "Please select a batch.", vbExclamation
End If
End Sub
Group related OptionButtons together so that the user can select one option from that group.
12. Frame and ToggleButton Controls
Frame Control
A Frame groups related controls visually. For example, place Morning and Evening OptionButtons inside a Frame captioned "Batch Selection".
Example 11: Configure a Frame
Private Sub UserForm_Initialize()
Me.fraBatch.Caption = "Batch Selection"
End Sub
ToggleButton Control
A ToggleButton switches between pressed and unpressed states. Its value can be used to enable or disable another control.
Example 12: Enable a TextBox
Private Sub tglEnableDetails_Click()
Me.txtDetails.Enabled = Me.tglEnableDetails.Value
End Sub
Add a ToggleButton named tglEnableDetails
and a TextBox named txtDetails.
The TextBox is enabled when the toggle is pressed.
13. MultiPage and TabStrip Controls
MultiPage and TabStrip controls help organize a form into sections. They are useful when a form contains many fields and would otherwise become difficult to navigate.
| Control | Typical Use |
|---|---|
| MultiPage | Provides separate pages that can contain different controls. |
| TabStrip | Provides tabs that can be used to change or organize displayed content. |
Example 13: Respond to a MultiPage change
Add a MultiPage control named mpgStudent with
two pages. Set their captions to Personal Details and
Academic Details.
Private Sub mpgStudent_Change()
Select Case Me.mpgStudent.Value
Case 0
Me.Caption = "Student Form - Personal Details"
Case 1
Me.Caption = "Student Form - Academic Details"
End Select
End Sub
The page index is zero-based, so the first page is 0 and the second page is 1.
14. SpinButton and ScrollBar Controls
SpinButton and ScrollBar controls help users adjust numeric values without typing every value manually.
Example 14: Configure a SpinButton
Add a SpinButton named spnQuantity and a
TextBox named txtQuantity.
Private Sub UserForm_Initialize()
With Me.spnQuantity
.Min = 1
.Max = 100
.SmallChange = 1
.Value = 1
End With
Me.txtQuantity.Value = Me.spnQuantity.Value
End Sub
Private Sub spnQuantity_Change()
Me.txtQuantity.Value = Me.spnQuantity.Value
End Sub
The SpinButton changes the quantity, and the TextBox displays the current value.
Example 15: Configure a ScrollBar
Add a ScrollBar named scrDiscount and a Label
named lblDiscount.
Private Sub UserForm_Initialize()
With Me.scrDiscount
.Min = 0
.Max = 100
.SmallChange = 1
.LargeChange = 10
.Value = 0
End With
Me.lblDiscount.Caption = "Discount: 0%"
End Sub
Private Sub scrDiscount_Change()
Me.lblDiscount.Caption = _
"Discount: " & Me.scrDiscount.Value & "%"
End Sub
The Label updates as the ScrollBar value changes. The percentage can then be used in a calculation.
15. Practical Project: Student Registration Form
Build a UserForm that collects student information and saves it to an Excel worksheet. This project combines TextBox, ComboBox, CheckBox, and CommandButton controls.
Required controls
| Control | Name | Purpose |
|---|---|---|
| TextBox | txtStudentName | Student name. |
| ComboBox | cmbCourse | Course selection. |
| TextBox | txtFees | Course fees. |
| CheckBox | chkPaid | Payment status. |
| CommandButton | cmdSave | Save registration. |
Example 16: Save student information
Create a worksheet named Students with these
headings in row 1: Student Name, Course, Fees, Paid.
Private Sub UserForm_Initialize()
With Me.cmbCourse
.Clear
.AddItem "Core Java"
.AddItem "Python"
.AddItem "C#"
.AddItem "Advanced Excel"
.Style = fmStyleDropDownList
.ListIndex = -1
End With
End Sub
Private Sub cmdSave_Click()
Dim ws As Worksheet
Dim nextRow As Long
Dim studentName As String
Dim courseName As String
Dim fees As Double
studentName = Trim$(Me.txtStudentName.Value)
courseName = Trim$(Me.cmbCourse.Value)
If Len(studentName) = 0 Then
MsgBox "Enter the student name.", vbExclamation
Me.txtStudentName.SetFocus
Exit Sub
End If
If Me.cmbCourse.ListIndex = -1 Then
MsgBox "Select a course.", vbExclamation
Me.cmbCourse.SetFocus
Exit Sub
End If
If Len(Trim$(Me.txtFees.Value)) = 0 Or _
Not IsNumeric(Me.txtFees.Value) Then
MsgBox "Enter valid course fees.", vbExclamation
Me.txtFees.SetFocus
Exit Sub
End If
fees = CDbl(Me.txtFees.Value)
If fees < 0 Then
MsgBox "Fees cannot be negative.", vbExclamation
Me.txtFees.SetFocus
Exit Sub
End If
Set ws = ThisWorkbook.Worksheets("Students")
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
If nextRow < 2 Then nextRow = 2
ws.Cells(nextRow, "A").Value = studentName
ws.Cells(nextRow, "B").Value = courseName
ws.Cells(nextRow, "C").Value = fees
ws.Cells(nextRow, "D").Value = Me.chkPaid.Value
MsgBox "Student registration saved successfully.", vbInformation
End Sub
Students worksheet and all controls with the
exact names shown above. Save the workbook as
.xlsm to preserve VBA code.
16. Common Mistakes to Avoid
- Writing code that references controls that have not been created.
- Using the wrong control name in an event procedure.
- Forgetting to populate a ComboBox or ListBox.
- Reading a selection before checking whether an item is selected.
- Assuming all controls use the same properties and events.
- Not validating user input before calculations.
- Forgetting to create the destination worksheet.
- Using confusing captions or poorly organized controls.
- Not testing the form with empty and invalid values.
- Saving the workbook in a format that does not preserve VBA code.
17. Practice Exercises
- Create a UserForm with a Label, TextBox, and CommandButton.
- Display the TextBox value in a message box.
- Create a ComboBox containing five course names.
- Display the selected course after validating the selection.
- Create a ListBox for student names.
- Use a CheckBox to indicate payment status.
- Use two OptionButtons to select a batch.
- Group related OptionButtons inside a Frame.
- Use a ToggleButton to enable or disable a TextBox.
- Use a SpinButton to adjust a quantity.
- Use a ScrollBar to select a percentage.
- Build a MultiPage form with Personal and Academic pages.
- Create a student registration form with input validation.
- Save student details to a worksheet using a CommandButton.
- Test every control using valid, invalid, and empty input.
Important Points to Remember
- The Toolbox provides controls for designing UserForms.
- Use the Properties window to configure names, captions, sizes, and behavior.
- Use TextBoxes to collect input and Labels to display information.
- Use ComboBoxes and ListBoxes to display selectable items.
- Use CheckBoxes for independent True/False options.
- Use OptionButtons for choices within a group.
- Use CommandButton click events to execute VBA procedures.
- Use SpinButtons and ScrollBars to adjust numeric values.
- Validate input before saving data to worksheets.
- Save macro-enabled workbooks in
.xlsmformat.
Summary
The VBA Toolbox contains controls such as Labels, TextBoxes, CommandButtons, ComboBoxes, ListBoxes, CheckBoxes, OptionButtons, Frames, MultiPage controls, SpinButtons, and ScrollBars. These controls help create interactive Excel UserForms.
By configuring control properties, writing event procedures, validating input, and connecting controls to worksheet operations, you can build practical applications such as student registration forms, employee entry systems, fee calculators, and data-management tools.