Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
Passing Control Values back to a Spreadsheet, Passing range values to the UserForm Controls
In this lesson we will be looking at how we can pass values from the Worksheet to a Control or any number of Controls on a UserForm and also look at how we can do the opposite which is to pass the value from a Control back to the Worksheet. Both of these methods are used in almost every UserForm as most UserForms are used to collect information from the User and then pass them back into a spreadsheet in specified ranges. We will start off by looking at how we can first get the values from a Worksheet into the Controls of a UserForm. There are many ways that this can be done and which way we use will basically depend on the intent and purpose of the UserForm itself.
The first method we will look at is how we can get the values from one specific cell into a TextBox. There are two commonly used ways to do this, and these are:
Via the ControlSource of the Control
This is used to identify the location of the data to set or store the Value Property of a Control. However, it is important to note that the Control source is a two way streak. By this, I mean that changes to a cell that is being used as the ControlSource in another Control are automatically passed to the Control and changes made to the Value Property of the Control are automatically passed to the ControlSource range. A valid setting for the ControlSource Property of a Control is simply a string representing the range address. So for example, if you were to link a ControlSource to cell A1 of the active Worksheet, you would simply type A1 into the ControlSource Property via the Properties window for that particular Control.
It is very important to note that simply nominating a cell address as we have shown above would mean the Control would be linked to the active Worksheet at the time the UserForm is shown. If you need to specify a specific range, ie; only on a specific sheet, there are two ways in which this can be done.
One would be to type Sheet1!A1 into ControlSource Property via the Properties window for that particular Control or we would name the cell we wanted as the linked ControlSource and then simply type in the name of that cell, eg; MyRange. The second option shown here, ie; the named range is the preferred option as it means should the Worksheet housing our range have more rows, columns, cells etc; inserted/deleted our ControlSource will still be linked to the correct range.
Naturally, this can also be done at Run Time and this would most likely be achieved either by placing some code such as shown below in the Initialize Event of the UserForm.
TextBox1.ControlSource="MyRange"
Note in the above code, it is assumed that the named range MyRange is a single cell. The reasons for doing this at Run Time as opposed to Design Time, ie; in the Properties window, can vary for many reasons. For example, you may wish for the ControlSource of TextBox1 to be linked to a single cell within a range that houses the highest value. Possibly the best way to achieve this or anything similar would be to use some code as shown below:
In the above example, we have used two variables, iMax which we used to store the highest number within the range MyRange. iMax obtains its value via the use of the Worksheet function Max. We then used the Find method to search within the range MyRange and locate the number being represented by iMax. We then Set the range variable rContSource to the cell which is found to contain the maximum number. Lastly, we then passed the address of rContSource to the ControlSource of TextBox1. Using a method like this will ensure that TextBox1 on the UserForm is always linked (via ControlSource) to the cell within the range MyRange which houses the highest number within that range.
Possibly one of the drawbacks to using the ControlSource Property and linking to a cell is that if the user types into the Control which is linked to a cell on the Worksheet, the value is automatically passed to the Worksheet the moment Focus leaves that Control. This can be a major problem should the user decide to change it or realise that he has typed in an incorrect entry and wishes to revert back to its original value. One way to overcome this potential problem is to store the original value of the ControlSource inside the Tag Property of the Control. This could be done very simply by adding the line of code below to the above Procedure.
TextBox1.Tag = rContSource Value
Then, on the UserForm, either via the click of a CommandButton or another method have the code below run.
Range(TextBox1.ControlSource) = TextBox1.Tag
This would automatically transfer the original value back to the correct cell, which in turn would automatically feed to the Value Property of the Control.
Via the Value Property
The second method we could use is to simply pass the Value of a cell directly to the Value Property of a specified Control. This, in effect would do the same as the ControlSource, however, the Value passed to the Control would not be dynamic or linked to its source. Again, probably the most common way for this to be done would be to simply use some code like below within the Initialize Event of a UserForm.
TextBox1.Value = Range("MyRange").Cells(1,1)
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.
©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved