VBA UserForm Controls – A Guide for Everyone

by Paul Kelly | Apr 30, 2016 | UserForms | 25 comments

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

The Webinar

If you are a member of the website, click on the image below to view the webinar for this post.

(Note: Website members have access to the full webinar archive.)

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.

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

Function	Operation	Example
Caption	Get/Set the text	checkbox.Caption = "Apple"
Value	Get the checked state	If checkbox.Value = True Then
Value	Set the checked state	checkbox.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.

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

Function	Operation	Example
Text	Get\Set the text	textbox1.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

Function	Operation	Example
Text	Set the text	textbox1.Text = "Apple"
Text	Get the text	sFruit = textbox1.Text
Value	Set the text	textbox1.Value = "Apple"
Value	Get the text	sFruit = 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

Function	Operation	Example
AddItem	Add an item	listbox.AddItem "Spain"
Clear	Remove all Items	combo.Clear
List	Add a range of items	combo.List = Range("A1").Value
ListCount	Get the number of items	cnt = combo.ListCount
ListIndex	Get/set selected item	Idx = combo.ListIndex combo.ListIndex = 0
ListRows	Get/set number of items displayed	NoItems = combo.ListRows combo.ListRows = 12
RemoveItem	Remove an item	combo.RemoveItem 1
Value	Get the value of selected Item	Dim 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.

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

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.

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

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

Our data is stored as follows

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

Function	Operation	Example
AddItem	Add an item	listbox.AddItem "Spain"
Clear	Remove all Items	listbox.Clear
ColumnCount	Set the number of visible columns	ComboBox1.ColumnCount = 2
ColumnHeads	Make the column row visible	ComboBox1.ColumnHeads = True
List	Range to Listbox ListBox to Range	Listbox.List = Range("A1:A4").Value Range("A1:A4").Value = Listbox.List
List	Update a column value	Listbox.List(1,2) = "New value"
ListCount	Get the number of items	cnt = listbox.ListCount
ListIndex	Get/set selected item	Idx = listbox.ListIndex combo.ListIndex = 0
RemoveItem	Remove an item	listbox.Remove 1
RowSource	Add a range of values from a worksheet	ComboBox1.RowSource = Sheet1.Range("A2:B3").Address
Value	Get the value of selected Item	Dim 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.

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.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

25 Comments

Ludo on May 10, 2016 at 10:16 am

Hi Paul,

Just noted a error in the checkbox text:

‘ Set the check on
CheckBoxTerms.Value = True

‘ Set the check off
CheckBoxTerms.Value = True ‘<<<< should be False.

Best regards,
Ludo
Reply
- Paul Kelly on May 10, 2016 at 11:11 am
  
  Thanks Ludo, for pointing that out. I’ve updated the post with the correct value.
  Reply
Nandor Dudas on May 13, 2016 at 2:50 pm

Hi,
for num: case asc(“0”) to asc(“9”),
for alpha: case asc(“a”) to asc(“z”), asc(“A”) to asc(“Z”),
for dates: case asc(“/”) to asc(“9”) 🙂
Reply
Henry on May 16, 2016 at 6:11 am

Though I only had a quick look, I think application.EnableEvents =… should be used in order not to trigger form control events … especially of hidden forms especially when they depend on ranges.
Reply
Ludo on May 19, 2016 at 12:18 pm

Hi Paul,

I’m rewriting a application (Add-In [13 userforms]) for the company and i do have a question regarding the userform(s).

what i do, is as follow:

I check if a configuration file (INI file) is present.
if not, I show a configuration userform (8 Page tabs – 73 controls) .
I enter all the needed data into the appropriate textboxes / listboxes / checkboxes and write this data into a Configuration.INI file.
The data is also placed into the Add-In worksheet / worksheets.
I unload the userform.

The next time i use the add-in, the configuration.INI file is present, so, i read the INI file into the Add-In worksheet(s).

The Add-In worksheet(s) use named ranges.

When i need data (names, numbers, file paths, …), i’ll read the corresponding worksheet named range.
No need to load the userform.

I see you use following construction in your main procedure code:
Dim frm As New UserForm
and the accompanying property Get / Let in the userform module.

I would like to know if this approach could add benefits against the way i’m working now.

How would you approach this?
Or am I doing it the right way?

I noticed also, if I use your approach with the userforms, and need data from one userform to use in an other sub that i needed to
define the userform on top of a regular module as public like:
Public MyUserform As New frmConfiguration
Then i can use code like ConfigPath = MyUserform.ConfigPath where ever i need it in the whole project.

Best regards,
Ludo
Reply
- Paul Kelly on May 21, 2016 at 10:39 am
  
  Hi Ludo,
  
  The way you are approaching is mostly okay but using the workbook for temporary storage can be slow and awkward.
  
  I would normally have a Class module for storing the settings.
  When the application loads you would read from the ini file to the class. When the application is finished you would write the values back to the file.
  
  When the UserForm is displayed you read from the class to the UserForm controls. When the user clicks Ok you write any changed values back to the class and unload the UserForm.
  
  If you have further questions about this drop me an email.
  Regards
  Paul
  Reply
  - Ludo on May 21, 2016 at 12:15 pm
    
    Hi Paul,
    
    Thanks for this verry clear answer.
    Something to take into account.
    
    Best regards,
    Ludo
    Reply
  - Juan Carlos on July 22, 2016 at 1:36 pm
    
    I’d love to see an example! Thank you
    Reply
Ludo on June 20, 2016 at 5:43 pm

Hi Paul,
Here i’m back with a question regarding Textbox and Pages on a userform.

I have a Userform with a Close button, a Previous and Next button on the bottom of it. Above these buttons i have 7 Pages (MultiPage object). What i notice is following behaviour: when i enter text into the last textbox on the Page, the Change event fires (= normal) , so i can do some stuff here. When i click then on the Next/Previous button, who are outside the Page, the textbox Exit event doesn’t fire. Is there a way to fire the textbox Exit event in the Page when click on a button outside the Page?

Best regards,
Ludo
Reply
Mario on June 25, 2016 at 6:48 pm

Hi Paul

The data generally are never ordered, should include the “False” argument in the formula, to avoid errors in these cases.

TextBoxCapital.Value = WorksheetFunction.VLookup(sCountry, Rg, 2, False)

Regards.
Reply
- Paul Kelly on June 27, 2016 at 11:46 am
  
  Hi Mario, I’ve written a post all about VLookup here.
  
  Regards
  Paul
  Reply
Quinton on October 26, 2017 at 2:25 pm

Hi Paul:

In a similar vein to Ludo’s question on 19 May ’16, I am investigating the [Public Property] Let statement to pass a variable into a Userform. However, I am attempting to use that variable to affect display of controls on the Userform. There are two checkboxes on the form, and I would like to pass an integer (set to 0, 1 or 2) to determine display of neither, the first or both checkboxes, by their .Visible switches.

The questions are:
– Can the Let statement be used in determining control display?
– If so, where is the statement placed in the Userform code?
– I have attempted to place the Let statement either in the Initialize or Activate procedures.
– this action changes the End Sub statement to End Property, which I manual correct.
– upon compiling, the debugger stops at the line in the procedure before the Let statement, claiming an unexpected End Sub.

Any advice you can provide would be welcome.

Thank you.
Reply
- Paul Kelly on November 1, 2017 at 11:17 am
  Hi Quinton,
  
  You can use the Let property to pass a value to the UserForm. You can then store this value in a variable in the UserForm.
  
  UserForm code
  
  Private m_lDisplay As Integer Public Property Let Display(ByVal lDisplay As Long) m_lDisplay = lDisplay End Property
  
  Calling the UserForm
  
  Dim frm As New UserForm1 ' pass the display value frm.Display = 1 ' display the userform frm.Show
  Reply
  - Quinton on November 3, 2017 at 9:36 pm
    
    Hi Paul:
    
    Your answer gave me enough to implement my plan. I hadn’t seen the Private variable declaration and the assignment of the passed value to the form’s variable (m_lDisplay) in other reference examples. Once I determined that setting the control visibility had to be done within the Let statement, it all worked correctly.
    
    As a follow-up question, the definition of the ByVal passed variable (lDisplay) as a Long (integer), then assigning it to the form’s Int variable (m_lDisplay), is that done as a pre-caution, or is it a necessity when passing integers in?
    
    Thanks for the help.
    
    Quinton
    Reply
Angus Arrol on July 2, 2018 at 6:14 pm

Hi Paul,

In Full Example 2, in the Userform_Initialize() code, is there an error in the assignment to add the array to the combobox:
ComboBoxCountry.List = shCity.Range(“A1:A4”).Value ‘<<<should be "A2:A5" ?

since row (1) is the title row?

Best regards,
Angus
Reply
- Paul Kelly on July 3, 2018 at 8:50 am
  
  Hi Angus,
  
  Thanks for pointing that out. I’ve updated the post.
  
  Paul
  Reply
Jake on July 5, 2018 at 11:01 am

Hi Paul,
Your posts and information are new to me and still going through it. Working on making a class for userform controls (minus labels and mutitab objects) to use one module each to handle the import and export of the data to a worksheet. Have you every done that or heard of it? Better yet, is it already known not to be doable? Thanks for your time in making the the site and responding to so many posts and questions.
Jake
Reply
Peter Szedlacsek on October 2, 2018 at 11:28 am

Hi Paul,

I created the list box example based on the almost verbatim copy of your code.
At the command “PrintCollection frm.Countries” I am getting a run time error “Object Required …..” and the print doesn’t work. The selected items are not transferred properly from the form module to the main module.
Your help is greatly appreciated.
I enclosed the copy of my code below. The only difference is that I used the default name “UserForm1” for the user form and changed the “Dim” statement in the main module accordingly.
Thanks,
Peter Szedlacsek
UserForm1 code
Private Sub UserForm_Activate()
Dim list_array(5) As String
ListBoxCountry.Clear
list_array(0) = “a”
list_array(1) = “b”
list_array(2) = “c”
list_array(3) = “d”
list_array(4) = “e”
ListBoxCountry.List = list_array
ListBoxCountry.ListIndex = 0
End Sub
‘ USERFROM CODE
‘ 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
‘ Sub to display the UserForm
Sub DisplayMultiCountry()
Dim frm As New UserForm1
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
Reply
- Paul Kelly on October 2, 2018 at 1:51 pm
  
  Hi Peter.
  
  It should have the following at the top of the UserForm code:
  
  Private m_CollCountries As Collection
  
  I have updated the post.
  Reply
Jarvis on May 8, 2019 at 8:13 pm

Hello all! I have been given the task of building a userform which output data from access database (completed). A part of the task is to output the data access into an excel worksheet (completed, then to start an history page of the orders that have been searched (incomplete). I am fairly new to vba coding, but I have a question that has been stumping me for a while. Here’s my line of code:

Sheet1.Range(“A2:A3”).EntireRow.Copy Sheet2.Range(“A2:A3”).EntireRow (copies the entire row)

What I am wanting to do in sheet 2 the History page is add more search order values that are search for by the user, so we can track their progress. So, if you understand what I am needing to do, please feel free to offer me an helping hand because I need 1.

Thanks Jarvis
Reply
Ludo on September 4, 2019 at 11:54 am

Hi Paul,
I’m struggling for hours to set the focus on a command button.
I have a userform with 7 textboxes and 5 command buttons.
As long as there’s no valid entry into these textboxes, i disable the ‘Save to Database’ button.
Once the criteria is met, I enable the ‘Save to Database’ button.
the tab order is 1 to 7 for the textboxes and 8 for the ‘Save to Database’ button, and so on for the following commandbuttons (Clear, Close, Recall last entry & Delete last entry).
The problem i’ll have is, when the ‘Save to Database’ button is enabled, the focus jumps to the next commandbutton, the one with taborder 9 (= Clear button).
How can I reset the focus to the ‘Save to Database’ button?
I tried to set de ‘Default’ value to True for the ‘Save to Database’ button, but this is also not working.
Best regards,
Ludo
Reply
- Paul Kelly on September 4, 2019 at 12:39 pm
  
  What happens if you use the SetFocus method of the button?
  Reply
  - Ludo on September 5, 2019 at 5:58 am
    
    Hi Paul,
    When i set the focus to the ‘Save to database’ button (tab index = 7) after enable this button, the focus goes to the Close button (tab index = 9).
    
    ‘check if all entries are filled with data
    If CheckData(Me) = True Then
    Me.cmdCopytoDataBase.Enabled = True
    Me.cmdCopytoDataBase.SetFocus < 0 Then
    Me.tbHeightD.Value = Replace(Me.tbHeightD.Value, “.”, “,”, 1, , vbTextCompare)
    End If
    ‘check if value is numeric
    ‘if not show error message and retry
    If Not IsNumeric(Me.tbHeightD.Value) Then
    sErrMsg = WRONG_ENTRY1 & vbCrLf
    sErrMsg = sErrMsg & WRONG_ENTRY2 & vbCrLf & vbCrLf
    sErrMsg = sErrMsg & TRY_AGAIN
    
    frmErrMsg.Show
    With Me.tbHeightD
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
    End With
    Cancel = True
    
    ‘check if entry > MAXIMUM_DISPLAY_HEIGHT
    ElseIf Me.tbHeightD.Value > MAXIMUM_DISPLAY_HEIGHT Then
    sErrMsg = WRONG_ENTRY1 & vbCrLf
    sErrMsg = sErrMsg & WRONG_ENTRY3 & ” – > ” & MAXIMUM_DISPLAY_HEIGHT & vbCrLf & vbCrLf
    sErrMsg = sErrMsg & TRY_AGAIN
    
    frmErrMsg.Show
    With Me.tbHeightD
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
    End With
    Cancel = True
    
    ‘check if entry < than the MINIMUM_DISPLAY_HEIGHT
    ElseIf Me.tbHeightD.Value ” & MINIMUM_DISPLAY_HEIGHT & vbCrLf & vbCrLf
    sErrMsg = sErrMsg & TRY_AGAIN
    
    frmErrMsg.Show
    With Me.tbHeightD
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
    End With
    Cancel = True
    Else
    ‘
    ‘display the shimm thickness for this mounting point
    Me.lblSpacerThicknessD.Caption = DEFAULT_DISPLAY_HEIGHT – Me.tbHeightD.Value
    ‘format value
    Me.lblSpacerThicknessD.Caption = Format(Me.lblSpacerThicknessD.Caption, “0.0”)
    ‘
    UpdateMountedSpacerPlate
    ‘
    blnBailOut = False
    ‘
    ‘check if all entries are filled with data
    If CheckData(Me) = True Then
    Me.cmdCopytoDataBase.Enabled = True
    Me.cmdCopytoDataBase.SetFocus
    End If
    End If
    
    End Sub
    
    I was thinking if following trick would do the job (need to check):
    – If CheckData(Me) = True , set a boolean flag (example: blnValidData)
    – enable the ‘Copy to DataBase’ button (focus goes to the tab index 8 button) something like:
    ‘check if all entries are filled with data
    If CheckData(Me) = True Then
    Me.cmdCopytoDataBase.Enabled = True
    blnValidData = True
    End If
    
    – set focus to the textbox with tabindex 6 (= textbox just before the ‘Copy to Database’ button
    – fire the exit event, check if the blnValidData is set, if so exit the textbox immediately.
    – hopefully the ‘Copy to Database’ button will get the focus.
    
    p.s.: I can send you the workbook too, there are no secrets in it. 🙂
    Reply
Ludo on September 5, 2019 at 5:59 am

Hi Paul,
When i set the focus to the ‘Save to database’ button (tab index = 7) after enable this button, the focus goes to the Close button (tab index = 9).

‘check if all entries are filled with data
If CheckData(Me) = True Then
Me.cmdCopytoDataBase.Enabled = True
Me.cmdCopytoDataBase.SetFocus < 0 Then
Me.tbHeightD.Value = Replace(Me.tbHeightD.Value, “.”, “,”, 1, , vbTextCompare)
End If
‘check if value is numeric
‘if not show error message and retry
If Not IsNumeric(Me.tbHeightD.Value) Then
sErrMsg = WRONG_ENTRY1 & vbCrLf
sErrMsg = sErrMsg & WRONG_ENTRY2 & vbCrLf & vbCrLf
sErrMsg = sErrMsg & TRY_AGAIN

frmErrMsg.Show
With Me.tbHeightD
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Cancel = True

‘check if entry > MAXIMUM_DISPLAY_HEIGHT
ElseIf Me.tbHeightD.Value > MAXIMUM_DISPLAY_HEIGHT Then
sErrMsg = WRONG_ENTRY1 & vbCrLf
sErrMsg = sErrMsg & WRONG_ENTRY3 & ” – > ” & MAXIMUM_DISPLAY_HEIGHT & vbCrLf & vbCrLf
sErrMsg = sErrMsg & TRY_AGAIN

frmErrMsg.Show
With Me.tbHeightD
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Cancel = True

‘check if entry < than the MINIMUM_DISPLAY_HEIGHT
ElseIf Me.tbHeightD.Value ” & MINIMUM_DISPLAY_HEIGHT & vbCrLf & vbCrLf
sErrMsg = sErrMsg & TRY_AGAIN

frmErrMsg.Show
With Me.tbHeightD
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Cancel = True
Else
‘
‘display the shimm thickness for this mounting point
Me.lblSpacerThicknessD.Caption = DEFAULT_DISPLAY_HEIGHT – Me.tbHeightD.Value
‘format value
Me.lblSpacerThicknessD.Caption = Format(Me.lblSpacerThicknessD.Caption, “0.0”)
‘
UpdateMountedSpacerPlate
‘
blnBailOut = False
‘
‘check if all entries are filled with data
If CheckData(Me) = True Then
Me.cmdCopytoDataBase.Enabled = True
Me.cmdCopytoDataBase.SetFocus
End If
End If

End Sub

I was thinking if following trick would do the job (need to check):
– If CheckData(Me) = True , set a boolean flag (example: blnValidData)
– enable the ‘Copy to DataBase’ button (focus goes to the tab index 8 button) something like:
‘check if all entries are filled with data
If CheckData(Me) = True Then
Me.cmdCopytoDataBase.Enabled = True
blnValidData = True
End If

– set focus to the textbox with tabindex 6 (= textbox just before the ‘Copy to Database’ button
– fire the exit event, check if the blnValidData is set, if so exit the textbox immediately.
– hopefully the ‘Copy to Database’ button will get the focus.

p.s.: I can send you the workbook too, there are no secrets in it. 🙂
Reply
Jim on July 20, 2021 at 6:16 pm

Is there a keyboard method to get controls onto a form? I am blind and can’t use a mouse efficiently.
Reply

VBA UserForm Controls – A Guide for Everyone

The Webinar

Introduction

The UserForm Controls

Properties of the Controls

Adding the Code

Common control functions

The CheckBox

The CheckBox Cheat Sheet

Turning the CheckBox on or off

Checkbox Event with example

The Label

The Label Cheat Sheet

Setting the Label Text

The TextBox

The TextBox Cheat Sheet

Setting the Textbox Text

Making a TextBox numeric only

Using a Date Control

The ComboBox

The ComboBox Cheat Sheet

Filling the Combobox with data

Filling the ComboBox from a range

Filling the ComboBox – No duplicates

VBA ComboBox – Full Example 1

VBA ComboBox – Full Example 2

The ListBox

The ListBox Cheat Sheet

What’s Next?

25 Comments

Submit a Comment Cancel reply

You are not logged in