XL97: Using the RefEdit Control with a UserForm

• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel 97, there is no Help topic for the RefEdit control. This article describes the RefEdit control and provides information about using the RefEdit control in a UserForm.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

A RefEdit control is similar in behavior and appearance to an edit box control with an InputType property that you set to xlReference. (The edit box control is available in Microsoft Excel 5.0 or 7.0 on the Forms toolbar in dialog sheets.) The RefEdit control differs from an edit box control in that the RefEdit control contains a button that allows you to collapse the UserForm to easily select a cell range.

Using an Edit Box

The RefEdit control imitates the behavior of the reference edit boxes that are built into Microsoft Excel. To illustrate how the built-in reference edit boxes work in Microsoft Excel, try the following steps:

1. In a new worksheet, click Function on the Insert menu to display the Function Wizard.

2. Click SUM in the Function Name list box and click OK.

1. Click the button with the red arrow in the edit box.

1. Drag the pointer over cells in the worksheet.

1. Click the button in the edit box to expand the Function Wizard.

2. Click OK or Cancel.

Using the RefEdit Control on a UserForm

The following example illustrates how to create a UserForm that contains a RefEdit control and a CommandButton control. When the UserForm appears, you input a reference in the RefEdit control and click OK. When you click OK, the range that you referenced is formatted with a red background, and the UserForm is unloaded. To illustrate how the RefEdit control works in Microsoft Excel, try the following steps:

1. In a new workbook, press Alt+F11 to activate the Visual Basic Editor.

2. On the Insert menu, click UserForm.

1. In the Toolbox dialog box, click RefEdit, and draw the control on UserForm1.

NOTE: If there is no RefEdit button in the Toolbox, use the following steps:

   a. On the Tools menu, click Additional Controls.

   b. In the Additional Controls dialog box, select the RefEdit.Ctrl check
      box, and then click OK.

      Sub CommandButton1_Click()

          Dim SelRange as Range
          Dim Addr as String

          'Get the address, or reference, from the RefEdit control.
          Addr = RefEdit1.Value

          'Set the SelRange Range object to the range specified in the
          'RefEdit control.
          Set SelRange = Range(Addr)

          'Apply a red pattern to the SelRange.
          SelRange.Interior.ColorIndex = 3

          'Unload the userform.
          Unload Me

      End Sub