 |
|
|
|
Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
Passing Control Values back to a Spreadsheet
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)