»

by Paul Kelly | Apr 30, 2016 | UserForms

“I want to think that there is someone with sound judgement at the controls” – Martin Feldstein

Contents [hide]

Introduction

In the first post on UserForms we looked at the general use of the UserForm.

In this post we are going to look at the individual VBA controls and how to use them. We will cover the most commonly used VBA controls. These are the Checkbox, ComboBox, CommandButton, Label, ListBox and TextBox. For each control, I will cover their most common uses and provide plenty of code examples you can use in your own projects.

The UserForm Controls

We add controls to the UserForms to allow the user to make selections, enter text or click a button. To add a control to a UserForm we do the following

  • Go to the Toolbox dialog – if not visible select View->Toolbox.
  • Click on the control icon you want to add – the icon will appear selected(pressed in) until you click another one.
  • Put the cursor over the UserForm.
  • Hold down the left mouse button and drag until the control is the size you want.
  • To select or move a control click on the Select Objects icon(very left one) on the toolbox and this will put you in select mode.

The following table shows a list of the common controls

Control Description
CheckBox Turn item on/off
ComboBox Allows selection from a list of items
CommandButton Click to perform action
Label Displays text
ListBox Allows selection from a list of items
Textbox Allows text entry

Properties of the Controls

The screenshot below shows the three important Windows when adding controls to a UserForm. These are - The Properties Window.

  • The Toolbox Dialog.
  • The UserForm design Window.

media-gmVYUo

UserForm Windows

If you click on any control or the UserForm itself you will see the properties of the selected item displayed in the Properties window. You can change the name, caption etc. in this Window.

To change the name of the UserForm do the following

  • Click on the UserForm in the Project window or click on the UserForm itself.
  • Click in the (Name) field of the Properties window.
  • Enter the new name in this field.

Adding the Code

You can view the code of the UserForm in the following ways

  • Double click on the UserForm.
  • Right click on the UserForm itself and select View Code.
  • Right click on the UserForm in the Project windows and select View Code.

Common control functions

The following table shows the most commonly used functions that are available to all controls.

Function Operation Example
Enabled Enable/Disable control combobox.Enabled = True
textbox.Enabled = False
SetFocus Sets the focus to the control (cannot use with the Label) combobox.SetFocus
Visible Show/Hide control combobox.Visible = True
textbox.Visible = False		 
' https://excelmacromastery.com/
Private Sub checkboxAddNotes_Click()

' Enable texbox when checkbox set to true
If checkboxAddNotes.Value = True Then
textboxNotes.Enabled = True
textboxNotes.SetFocus
Else
textboxNotes.Enabled = False
End If

End Sub

The CheckBox

The CheckBox Cheat Sheet

FunctionOperationExample

CaptionGet/Set the textcheckbox.Caption = "Apple"

ValueGet the checked stateIf checkbox.Value = True Then

ValueSet the checked statecheckbox.Value = False

The CheckBox is a simple control that allows the user set something to on or off. You will often see them used on web pages where you are asked to accept terms and conditions.

media-mnCzvV

Turning the CheckBox on or off

We can turn the CheckBox on or off by setting it to true or false

' Set the check on
CheckBoxTerms.Value = True

' Set the check off
CheckBoxTerms.Value = False

Checkbox Event with example

If we want to create an action when the user clicks on the checkbox then we create a checkbox event. This is simply a sub that runs when the checkbox is clicked.

To create this event simply click on the checkbox in design mode and you will get the following

Private Sub CheckBoxTerms_Click()

End Sub

The following code shows an example of how we use it

' https://excelmacromastery.com/
Private Sub CheckBoxTerms_Click()

If checkboxTerms.Value = True Then
buttonOk.Enabled = True
Else
buttonOk.Enabled = False
End If

End Sub

The Label

The Label Cheat Sheet

FunctionOperationExample

TextGet\Set the texttextbox1.Text = "Apple"

The label is the most simple control. Generally speaking, we don’t refer to it in the code. It is used to label the controls or display a short notice.

Setting the Label Text

You can set the text of the Label in the code using the Caption property

LabelAddress.Caption = "Customer Address"

The TextBox

The TextBox Cheat Sheet

FunctionOperationExample

TextSet the texttextbox1.Text = "Apple"

TextGet the textsFruit = textbox1.Text

ValueSet the texttextbox1.Value = "Apple"

ValueGet the textsFruit = textbox1.Value

Setting the Textbox Text

The textbox is used to allows the user to enter text. We can read or write from a text box as follows

TextBoxNotes.Value = "It was the best of times."

sNotes = TextBoxNotes.Value

The textbox has properties Text and Values. These are the same thing.

From MSDN: For a TextBox, any value you assign to the Text property is also assigned to the Value property.

The problem with the text box is that the user can enter anything. If the user is entering basic text then this is fine. If the text is to be used for a calculation or for looking up something then we need to validate it.

For example, if the user is going to pick a year between 2010 and 2019 we should use a ComboBox/Listbox that only contains valid years. Then we don’t need to validate the format and range of the user’s entry.

Making a TextBox numeric only

The following code prevents the user entering anything other than a number in the textbox

' https://excelmacromastery.com/
Private Sub textboxComments_KeyPress( _
ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case Is < vbKey0, Is > vbKey9
KeyAscii = 0
Beep
End Select

End Sub

Using a Date Control

If you the user to select a date you can use the MonthView control. It is one of the additional controls that comes with Visual Basic. It works quite well and looks like the standard date picker you see in most applications.

To add the MonthView control:

  • Go the the Visual Basic editor and make sure the Toolbox is visible(View->Toolbox if it’s not visible).

  • Select Tools and then Additional Controls from the menu.

  • Place a check on Microsoft MonthView Control, Version 6.0.

  • The MonthView control will now appear on the Toolbox.

To get the user selection from the MonthView control you can use the DateClick event as the following code shows

' https://excelmacromastery.com/
Private Sub MonthView1_DateClick( _ 
ByVal DateClicked As Date) 

' Store date in textbox 
TextBox1.Value = MonthView1.Value 

End Sub

For more information on the MonthView see these links:

Issues adding the MonthView – see top answer on this StackOverflow page

MSDN – Using the MonthView Control

The ComboBox

The ComboBox Cheat Sheet

FunctionOperationExample

AddItemAdd an itemlistbox.AddItem "Spain"

ClearRemove all Items combo.Clear

ListAdd a range of itemscombo.List = Range("A1").Value

ListCountGet the number of itemscnt = combo.ListCount

ListIndexGet/set selected itemIdx = combo.ListIndex

combo.ListIndex = 0

ListRowsGet/set number of items displayed NoItems = combo.ListRows

combo.ListRows = 12

RemoveItemRemove an item combo.RemoveItem 1

ValueGet the value of selected ItemDim sCountry As String

sCountry = combo.Value

The ComboBox is used to allow the user to select an item from a list. It is very similar to the listbox. The main difference is the listbox allows multiple selections.

In most cases we want to do four things with the ComboBox\ListBox

  • Fill it with data when the Form is loaded

  • Preselect an item.

  • Retrieve the currently selected item(s).

  • Perfom an action when the user selects a different item.

Filling the Combobox with data

We can fill the combobox one item at at a time using the AddItem property. 

comboBoxFruit.AddItem "Apple"
comboBoxFruit.AddItem "Pear"

You would normally use AddItem in a loop where you are checking each item before adding it

Dim cell As Range
' Fill items with first letter is A
For Each cell In Sheet1.Range("A1:A50")
If Left(cell.Value, 1) = "A" Then
comboBoxFruit.AddItem cell.Value
End If
Next

Filling the ComboBox from a range

If you want to fill the ComboBox from an entire range you can do it in one line of code 

comboBoxFruit.List = Sheet1.Range("A1:A5").Value

Normally when you fill a ComboBox you want to clear the existing contents first

' Clear any existing item
comboBoxFruit.Clear
' Fill the ComboBox
comboBoxFruit.List = Sheet1.Range("A1:A5").Value

Filling the ComboBox – No duplicates

If our range has multiple entries then we want to avoid adding the same item multiple times. We can do this using a Dictionary

' https://excelmacromastery.com/
Sub TestDuplicates()

' clear existing values
comboBoxFruit.Clear

' Fill given ComboBox from given range ignoring duplicates
FillComboNoDuplicates comboBoxFruit, Sheet1.Range("A1:A10")

End Sub

Sub FillComboNoDuplicates(cbo As ComboBox, rg As Range)

' Create dictionary
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim cell As Range, sItem As String
' Go through each item in range
For Each cell In rg
sItem = Trim(cell.Value)
' check if item already exists in dictionary
If dict.Exists(sItem) = False Then
' If doesn't exist then add to dictionary and combobox
dict.Add sItem, 1
cbo.AddItem sItem
End If
Next

' Clean up dictonary as we no longer need it
Set dict = Nothing

End Sub

VBA ComboBox – Full Example 1

The easiest way to show how these work is with an example. Imagine we have a list of countries and their capitals in cells A1:B196.

media-SoRsew

We want the user to select any country. When they do our UserForm will display the capital of that country. The screenshot below shows and example of this

media-olMDTJ

The first thing we want to do is fill the countries ComboBox when the form loads. We do this using the UserForm_Initialize event which we looked at in the first post on VBA UserForms.

Private Sub UserForm_Initialize()

End Sub

We can use the following code to fill the ComboBox from a range and set the selected item to be the first one. (Note we don’t need to clear the ComboBox here as the Initialize event is only used once – when the UserForm is created.)

' https://excelmacromastery.com/
Private Sub UserForm_Initialize()

' Add array to combobox
ComboBoxCountry.List = Sheet1.Range("A1:A196").Value

' Set the first item in combobox
ComboBoxCountry.ListIndex = 0

End Sub

When the user selects a country we want to display the capital city in the textbox. We use the Change Event of the ComboBox. To create this we simply double-click on the ComboBox and it will be automatically created.

' https://excelmacromastery.com/
Private Sub ComboBoxCountry_Change()

' Get the value from the combo box
Dim sCountry As String
sCountry = ComboBoxCountry.Value

' Get the range
Dim rg As Range
Set rg = Sheet1.Range("A1:B196")

' Use VLookup to find the capital of the country
TextBoxCapital.Value = _
WorksheetFunction.VLookup(sCountry, rg, 2)

End Sub

When the user clicks Ok we write the selected values to the Results worksheet

' https://excelmacromastery.com/
Private Sub buttonOK_Click()

With shResult

' Write the country the was selected
.Range("A1") = ComboBoxCountry.Value

' Write the postion of the selected country
.Range("A3") = ComboBoxCountry.ListIndex

' Write the capital of the country
.Range("A2") = TextBoxCapital.Value

End With

' Close the form
Unload Me

End Sub

VBA ComboBox – Full Example 2

A very commmon task to perform is to update a second ComboBox based on the selection of the first.

media-rRPbmk

Imagine we have two ComboBoxes – one contains the name of a country and one has a list of three cities from that country.

media-dGQGSF

When the user selects a country we update the city ComboBox with the cities from that country.

Our data is stored as follows

media-nTOhKV

Anytime the Country ComboBox value is set to a country we update the City ComboBox to contain the three cities from that country. This happens in two places

  • When we load the country combo box – the Initialize Event

  • When the user selects a country – the Change Event

The code for these is as follows

' https://excelmacromastery.com/
Private Sub UserForm_Initialize()

' Add array to combobox
ComboBoxCountry.List = shCity.Range("A2:A5").Value
ComboBoxCountry.ListIndex = 0

' Fill the city ComboBox
FillCityCombo ComboBoxCountry.ListIndex

End Sub

Private Sub ComboBoxCountry_Change()
' Fill the city ComboBox
FillCityCombo ComboBoxCountry.ListIndex
End Sub

In both cases we call our FillCityCombo Sub to fill the city ComboBox. It takes one parameter which is the position of the current country selection.

We use the position value to count from the top row of our worksheet range.

' https://excelmacromastery.com/
Sub FillCityCombo(ByVal row As Long)

' Get the city range from the given row
Dim rgCities As Range
Set rgCities = shCity.Range("B2:D2").Offset(row)

' Clear current city list
ComboBoxCity.Clear

' We transpose the range of columns e.g. B2:D2 to rows so 
' we can add to the ComboBox
ComboBoxCity.List = _
WorksheetFunction.Transpose(rgCities)

' Set the first city in list
ComboBoxCity.ListIndex = 0

End Sub

The ListBox

The ListBox is used in almost the same way as the ComboBox. The code in the ComboBox section above will work for the ListBox also.

The ListBox Cheat Sheet

FunctionOperationExample

AddItemAdd an itemlistbox.AddItem "Spain"

ClearRemove all Itemslistbox.Clear

ColumnCountSet the number of visible columnsComboBox1.ColumnCount = 2

ColumnHeadsMake the column row visibleComboBox1.ColumnHeads = True

ListRange to Listbox

ListBox to RangeListbox.List = Range("A1:A4").Value

Range("A1:A4").Value = Listbox.List

ListUpdate a column valueListbox.List(1,2) = "New value"

ListCountGet the number of itemscnt = listbox.ListCount

ListIndexGet/set selected itemIdx = listbox.ListIndex

combo.ListIndex = 0

RemoveItemRemove an itemlistbox.Remove 1

RowSourceAdd a range of values from a worksheet ComboBox1.RowSource = Sheet1.Range("A2:B3").Address

ValueGet the value of selected ItemDim sCountry As String

sCountry = listbox.Value

We can use the ListBox the same way as we used the ComboBox. The difference is how they are displayed

  • The ListBox displays a list of available items to select.

  • The ComboBox only displays the selected item. You have to click on the ComboBox to see the other available items.

The other major difference between them is that the ListBox allows multiple selections and the ComboBox doesn’t.

media-krBBoL

We can get all the selected items in the ListBox by reading through all the items and checking if each one is selected. In the code below we add all the selected items to a Collection.

' USERFROM CODE
' https://excelmacromastery.com/
Private m_CollCountries As Collection

' OK click event
Private Sub buttonOk_Click()
' Get the user selections
Set m_CollCountries = GetSelections
' Hide the UserForm
Hide
End Sub

' Returns the collection of countries
Property Get Countries() As Collection
Set Countries = m_CollCountries
End Property

' Get the selections from the ListBox
Private Function GetSelections() As Collection

Dim collCountries As New Collection
Dim i As Long

' Go through each item in the listbox
For i = 0 To ListBoxCountry.ListCount - 1
' Check if item at position i is selected
If ListBoxCountry.Selected(i) Then
' Add item to our collection
collCountries.Add ListBoxCountry.List(i)
End If
Next i

Set GetSelections = collCountries

End Function

' MAIN PROCEDURE CODE
' https://excelmacromastery.com/
' Sub to display the UserForm
Sub DisplayMultiCountry()

Dim frm As New UserFormCountryMulti
frm.Show

' Print the collection
PrintCollection frm.Countries

End Sub

' Sub used to print a collection to the Immediate Window(Ctrl + G)
Public Sub PrintCollection(ByRef coll As Collection)

Debug.Print "The user selected the following countries:"
Dim v As Variant
For Each v In coll
Debug.Print v
Next

End Sub

That concludes the two posts on UserForm(see first one here).

I hope you found them of benefit. If you have any questions or queries please feel free to add a comment or email me at Paul@ExcelMacroMastery.com.