The Ref Edit Control

A common requirement for custom dialog boxes is providing an interface in which the user can select a range of cells from a worksheet. Your program then uses the selected range for some specific task. The RefEdit control makes it easy to acquire a worksheet range from a form.

Several of Excel's dialogs and wizards contain RefEdit controls, including the chart wizard shown in Figure 6.8.

The RefEdit control allows the user to select a range from an existing Excel worksheet, and have the textual reference for the selected range automatically entered into the edit region of the control. You can also enter the range manually by typing in the text area of the control.

Selecting a worksheet range using Excel's chart wizard.

Selecting a worksheet range using Excel's chart wizard.

Vba Refedit

To test how a RefEdit control works, you don't even need any code. Just add a form to any VBA project. Draw a RefEdit control on the form and press F5 on your keyboard. Next, select a range from any worksheet and the reference will be added to the RefEdit control as shown in Figure 6.9.

RefEdit control-drop button

Selecting a worksheet range using a RefEdit control on a form.

RefEdit control-drop button

Selecting a worksheet range using a RefEdit control on a form.

Refedit Control Vba

You can also collapse the form by clicking on the drop button at the right of the RefEdit control prior to selecting the range.

You read the selected range from the RefEdit control with the Text or Value properties. Both properties are strings, so it doesn't matter which one you read. For example, the following line of code reads the value of the Text property of a RefEdit control named RefEdit1 to create a Range object:

Dim selRange As Range

Set selRange = Range(RefEdit1.Text)

After the selRange object variable is set, you can access its properties and methods as needed.

You will seldom use any properties of the RefEdit control other than the Name, Text, or Value properties (excluding the usual appearance and size properties). The Name property provides a meaningful name to the control for code readability. The Text or Value property provides you with the selected range, which is the task for which this control was designed.

There are several event procedures of the RefEdit control that you may find useful. The Enter(), Exit(), Change(), and DropButtonClick() events are triggered when the focus enters or exits the control, the text in the control is changed, or the drop button is pressed (as implied by their names); but be wary of referencing the RefEdit control in any of its own event procedures, as this may cause your program to lock up. The RefEdit control has a history of bugs (see the MSDN developer Web site at and search for RefEdit control) that have not yet been resolved. Instead, you have to find workarounds.

I recommend using the RefEdit control when you need a range selection from the user entered in a form; however, I further suggest that you do not try to read the range text entered in the RefEdit control from any of its own event procedures. Instead, you should read the text from the event procedure of another ActiveX control. The Click() event of a Command Button control works quite well as you will see later in this chapter.

You cannot use the RefEdit control on a modeless form. Doing so will cause Excel and VBA to lock up after showing the form and selecting a worksheet range.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment