RefEdit controls allow the user to select a range of cells with the mouse. While the user is selecting cells, the parent UserForm shrinks to the size of the RefEdit plus the title bar. The address of the selected range appears in the RefEdit and updates as the mouse is dragged, and the size of the selected range is displayed next to the cursor.
When the mouse is released, the UserForm returns to its original size, and the address remains in the RefEdit. When the RefEdit has focus, the indicated range is highlighted with the “marching ants” border.
The RefEdit actually contains a string variable, not a range. This allows the user to edit the address in the RefEdit, and in fact, the user can enter any text as if the RefEdit is a text box. This also allows the programmer to insert the address of a range (usually the current selection) into the RefEdit when loading the UserForm.
The Error Bar Utility that I introduced in Error Bars in Excel 2007 Charts makes use of this dual entry behavior, allowing a user to enter a constant or a range of cells for the error bar values.
The RefEdit control has some strange and wonderful idiosyncrasies which must be considered when designing a dialog. There are (at least) four issues that are important to keep in mind.
RefEdits must be placed directly on the UserForm itself. If you put a RefEdit in a frame or on a multipage, strange things will happen, including bizarre Excel crashes.
RefEdits must not be used on modeless forms. RefEdits on modeless forms will result in bizarre Excel crashes.
RefEdit event procedures should be avoided. RefEdit events do not behave reliably, and they may result in VBA errors which are difficult to debug.
References to RefEdits must be removed. When a UserForm is added to a VB project, Excel adds a reference to the Microsoft Forms 2.0 Object Library. This reference is required for proper operation of the UserForms in your project. To see what references your project has, select the project in the Project Explorer, then select References from the Tools menu.
When a RefEdit is added to a UserForm, Excel sometimes adds a reference to the Ref Edit Control. This was fine in earlier versions of Excel, but there appear to be incompatibilities in recent versions of the RefEdit library. These problems often are associated with an Unspecified but Painfully Frustrating Error.
To reduce the risk of RefEdit-related issues, you should uncheck the box in front of Ref Edit Control in the References dialog. Otherwise, there may be problems loading the add-in on another user’s machine, including the removal of RefEdits from the dialogs and other bizarre behavior.
It is also helpful to process the workbook or add-in’s code through an earlier version of Excel. I use a VM with Office 2000 installed on it for this purpose. Open Excel 2000, open the workbook or add-in, run Rob Bovey’s free Code Cleaner application, then compile the code and save the file.
It may also be necessary to remove RefEdit.exd files from a user’s computer if problems occur and unchecking the reference doesn’t fix them. these exd files are temporary files that contain instructions Excel uses to build RefEdits, and the exd may conflict with the RefEdits in the workbook. If this doesn’t work, the next step is to run Detect and Repair on Excel, then reinstall Office, then sacrifice the computer at the next full moon.