Select Ranges in the Worksheet from a UserForm

Select a range in Excel from a UserForm and have that range input into the form so that you can use it; this method is a visual method that allows you to select the desired cells by hand, while the form is still open.

To do this we use the RefEdit control.

Sections:

Select a Range from a UserForm

Get the Range into the Form

Loop through the Range in the Userform

Notes

Select a Range from a UserForm

All you have to do is to add the RefEdit control to the form; this takes care of everything needed to select the range within Excel.

Go to the VBA Window (Alt+F11) > Double-click the desired form from the Project window on the left (Ctrl+R to see the Project window) > view the Toolbox (View menu > Toolbox [Sometimes you have to first click the visible form to be able to see the toolbox]).

Once you place the RefEdit control onto the form, it will look like this:

Now that you have this control, you will be able to run your form, click the box in the right side of the RefEdit control and select any range from Excel.

Now we can see the range reference within the control at the bottom of the form and the next step is to do something with that range and bring it into the code section of the form, the backend.

Get the Range into the Form

To access the range reference from the RefEdit control, we use the Value property.

RangeReference = RefEdit1.Value

RangeReference is the variable that will hold the range reference - you can name this anything you want.

RefEdit1 is the name of the RefEdit control.

Value is the property that holds the range that we selected.

Store the Reference as an Actual Range

Once we have the range in the code, we can create another variable that will take that range reference, which is currently stored as text, and store it as a range, which means that we will then be able to run functions on it.

Set OurRange = Range(RangeReference)

Code so far:

'Get the range from the RefEdit Control
RangeReference = RefEdit1.Value

'Take the text range reference and turn it into a usable range reference.
Set OurRange = Range(RangeReference)

Now, we can do things like loop through this range using the OurRange variable.

Loop through the Range in the Userform

Following what we did in the last section above, we can use a simple For Loop in VBA to loop through all of the selected cells.

The loop will look like this:

For Each cell In OurRange

    'Inside the loop

Next cell

The full code, combined with the code that gets the range reference, looks like this:

'Get the range from the RefEdit Control
RangeReference = RefEdit1.Value

'Take the text range reference and turn it into a usable range reference.
Set OurRange = Range(RangeReference)

For Each cell In OurRange

    'Inside the loop

Next cell

Once you have the loop, you can do anything that you want inside of it by referencing cell.

Get the Value of the Cell

cell.Value

Put that inside of the code:

'Get the range from the RefEdit Control
RangeReference = RefEdit1.Value

'Take the text range reference and turn it into a usable range reference.
Set OurRange = Range(RangeReference)

For Each cell In OurRange

    'Display the value of the cell
    MsgBox cell.Value

Next cell

Here, the cell value will be output in a message box so that you can see if your code is working or not.

Get the Address of the Cell

cell.Address

Put that inside of the full code:

'Get the range from the RefEdit Control
RangeReference = RefEdit1.Value

'Take the text range reference and turn it into a usable range reference.
Set OurRange = Range(RangeReference)

For Each cell In OurRange

    'Display the address of the cell
    MsgBox cell.Address

Next cell

Here, the cell address is output in a message box so that you can see if your code is working or not.

Notes

There are many other things that you can do with cells once you loop through them, this tutorial is meant to show you how you can select the cells, loop through them, and then how you can do something with these cells. The two most common things done with cells are to use it's address and get it's value, which is why those examples are included above.

This code may look confusing but I promise that it is not difficult to learn once you use it a few times.

Download the sample workbook and work with this code until you become familiar with it. The code for the ranges is inside the code section for the Do Something with Range commandbutton; double-click that button on the form to get to the code section. Some parts of that section have been commented out so that can play around with the workings of the code.