VBA UserForms – A Guide for Everyone (Part 2)

 

“The system should treat all user input as sacred.” – Jef Raskin

A Quick Guide to the VBA UserForm




FunctionExamples




Declare and create Dim form As New userformCars


Declare and create Dim form As userformCars

Set form = New userformCars


Show as modalform.Show

OR

form.Show vbModal


Show as non modalform.Show vbModeless


UnloadPrivate Sub buttonCancel_Click()

  Unload Me

End Sub


HidePrivate Sub buttonCancel_Click()

  Hide

End Sub


Get\set the titleform.Caption = "Car Details"

CLICK HERE TO DOWNLOAD THE TEST DATA FOR THIS POST

Introduction

In the first post we looked at the general use of the UserForm. We saw how to

diplay it and how to retrieve data from it.

In this post we are going to look at the individual controls and how to use them.

Excel’s Format Cells User Form

User forms are made up of different types of controls such as buttons, list boxes, dropdown lists, checkboxes and textboxes.

In simple terms, we are asking the user to give us some information and then we are using this information when running the application.

The Controls

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

Go to the toolbox dialog – if not visible select View->Toolbox
Click on the control you want to add – the button for this control will appear flat
Put the cursor over the UserForm.
Hold down the left mouse button and drag until the size you want.

The following table shows a list of the common controls

ControlDescription




Label Displays text




TextboxAllows text entry


ComboBoxAllows selection from a list of items


ListBoxAllows selection from a list of items


CheckBoxTurn item on/off


CommandButtonClick to perform action




Properties of the Controls

Take a look at the following screenshot

UserForm Windows

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

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.
Type in the new name

Formatting the controls

Adding the Code

To view the code of the UserForm

Right click on the UserForm in the properties windows(or the UserForm itself) and select “View Code”.
You will see a Sub call UserForm_Click. You can delete this when you create your first sub.
If you double click on any control it will create the click event of that control if it doesn’t exist. It both cases will

go to the code which is very usefi;.

 

The controls

Let’s look at the controls and how we use them

Common control functions

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





FunctionOperationExample




EnabledEnable/Disable controlcombobox.Enabled = True

textbox.Enabled = False


SetFocusSets the focus to the control.

(cannot use with the Label).combobox.SetFocus


VisibleShow/Hide controlcombobox.Visible = True

textbox.Visible = False





The Enabled is often used when we want to prevent a user making a selecting for a particular control.

CheckBox

FunctionOperationExample




CaptionGet/Set the textcheckbox.Caption = "Apple"


ValueGet the checked stateIf checkbox.Value = True Then


ValueSet the checked statecheckbox.Value = False





The check box 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.

 

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 = True

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 clicks.

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

Private Sub CheckBoxTerms_Click()

End Sub

You can put any code you like here.

Label




FunctionOperationExample




TextGet\Set the texttextbox1.Text = "Apple"

The label is the simplest off controls and generally speaking we don’t use it in the code. It is used to identify the controls or explain something to the user.

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

LabelAddress.Caption = "Customer Address"

TextBox




FunctionOperationExample




TextSet the texttextbox1.Text = "Apple"


TextGet the textsFruit = textbox1.Text


ValueSet the texttextbox1.Value = "Apple"


ValueGet the textsFruit = textbox1.Value

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 want the user to pick a country for example we would allow them to select the country from a list. This means they cannot accidentally spell the name of the country wrong.

If we want to put limits on what the user can enter in a textbox then we need to be creative.

ComboBox




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.

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

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

[image error]

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 ComboBoxCountry_Change()

End Sub

 

We can use the following code to update the textbox

Private Sub ComboBoxCountry_Change()

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

' Use VLookup to find the capital of the country
TextBoxCapital.Value = _
WorksheetFunction.VLookup(sCountry, Sheet1.Range("A1:B196"), 2)

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.

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

 

ListBox




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


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

The listbox allows the user to select from a list of items

A complete example

 

What’s Next?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

If you are serious about mastering VBA then you may want to check out Build 11 VBA Modules Full VBA Applications

Get the Free eBook

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

 

 

The post VBA UserForms – A Guide for Everyone (Part 2) appeared first on Excel Macro Mastery.