Excel Training VBA 2 Lesson 16

Passing range values to UserForm Controls

Information Helpful? Why Not Donate | Free Excel Help >> Excel Training-Video Series >> Build Trading Models In Excel
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

Passing a Range of Cells back to a Control

Whilst the ControlSource Property is handy to use in some ways, it has one obvious limitation with that being that the ControlSource as shown in the above examples needs to be a single range.  If we were using a ComboBox or ListBox, we could probably assume that the Control would require more than one cell passed to it.  For example, if we had a list on a Worksheet and we wanted to pass that to the ComboBox so the User could make a selection, we would use either the RowSource Property or the AddItem Method.  With the former being the preferred method as it does not require us to loop through the range one cell at a time.  This has been shown in prior lessons, however, in prior lessons we did not cover the case where we may be using a ListBox and that ListBox will be storing the Values of numerous cells which reside in a range containing more than one column.  If we were using a ListBox as described in above, it would be quite feasible that we would also need to allow the User to select more than one item from the ListBox.  This is also described below.

Let's look at one very common problem with a ListBox Control, that is how to code it if the MultiSelect Property is set to fmMultiSelectMulti and/or we have more than one column of data. I have created a Workbook example of this which demonstrates just how we can make use of a ListBox that has these attributes.

You will see from the Workbook that I have coded it so a user can easily select which item(s) to delete from a Table. They are also given the option of deleting:  3 columns*1 row or 2 columns*1 row or 1 column*1 row. There is also a Checkbox to toggle selecting all/none of the items. Before you look at the code behind the ListBox, please read the text below from the Excel VBA help.

The ListCount property is read-only. ListCount is the number of rows over which you can scroll. ListRows is the maximum to display at once. ListCount is always one greater than the largest value for the ListIndex property, because index numbers begin with 0 and the count of items begins with 1. If no item is selected, ListCount is 0 and ListIndex is –1.

The Selected property is useful when users can make multiple selections. You can use this property to determine the selected rows in a multi-select list box. You can also use this property to select or deselect rows in a list from code.

The default value of this property is based on the current selection state of the ListBox. For single-selection list boxes, the Value or ListIndex properties are recommended for getting and setting the selection. In this case, ListIndex returns the index of the selected item. However, in a multiple selection, ListIndex returns the index of the row contained within the focus rectangle, regardless of whether the row is actually selected.

When a list box control's MultiSelect property is set to None, only one row can have its Selected property set to True. Entering a value that is out of range for the index does not generate an error message, but does not set a property for any item in the list.

Nice-to-have User-Friendly Features

Often when using a list which resides on a Worksheet to display to the User via a ComboBox or ListBox, the List itself may contain duplicates and also not be sorted in alphabetical order.  This can be very annoying for a user should they have to scroll through a List which contains duplicated items and the items are not sorted.  There are many ways in which we can use a Loop to move through the List and ensure that each item within the List is only added once via the AddItem method.  We could also incorporate some code within the Loop to ensure that the items are added in alphabetical order.  However, doing so would mean quite a lot of code, especially comparison code to ensure our List is unique and sorted.  This would also mean that our code could take some time to complete on a lengthy list, and if there is one thing that a User dislikes, that is having to wait for code to finish before they can move on.   This is why it is always best to use the RowSource Property whenever possible.  The code I will show you below can be used to populate a ListBox with only unique items from a List containing many duplicates and also have it in alphabetical order.

Sub SortAndRemoveDupes()
Dim rListSort As Range, rOldList As Range
Dim strRowSource As String

 'Clear Hidden sheet Column A ready for list
  Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear
 
 
 'Set range variable to list we want
  Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))

 'Use AdvancedFilter to copy the list to Column A _
  of the hidden sheet and remove all dupes
  rOldList.AdvancedFilter Action:=xlFilterCopy, _
               CopyToRange:=Sheet1.Cells(1, 1), Unique:=True
              
 'Set range variable to the new non dupe list
  Set rListSort = Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp))

    With rListSort
             'Sort the new non dupe list
             .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _
                   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
   
     'Parse the address of the sorted unique items
           strRowSource = Sheet1.Name & "!" & Sheet1.Range _
           ("A2", Sheet1.Range("A65536").End(xlUp)).Address
  
    Sheet1.Range("A1") = "New Sorted Unique List"
   With UserForm1.ListBox1
      'Clear old ListBox RowSource
      .RowSource = vbNullString
      'Parse new one
      .RowSource = strRowSource
  End With
 
End Sub

In the above example, we have used a hidden sheet to store our new list containing only unique items and also have it sorted in ascending order.  We generate our unique list via the use of Excel's advanced filter.  This alone can potentially speed up the code as opposed to a Loop by hundreds of times.  It also means that we can achieve creating a unique List with one line of code as opposed to the many that would be needed for a Loop.  Once we have created our unique list, we then simply sort it and then pass the address and the sheet name of the newly created list to a String Variable which is then used as the String for the RowSource Property.

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX