RefEdit Controls
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.
RefEdit Irregularities
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.
Andy Pope says
Due to the flakey behaviour of the refedit control I now use the Application.Input method.
With the Type parameter set to 8.
Jon Peltier says
Andy –
I use Application.Input as well, but it’s not as smooth looking as the RefEdit. Also, if I can convince my users (or their IT lackeys) to run Detect & Repair, most RefEdit problems are solved.
Daniel says
It’s a very interesting suggestion to remove the RefEdit.DLL reference from the VBA project — I tried it and it works, but why? Why need the reference in the first place?
Also, I’d like to add another pitfall using RefEdit controls. In my experience, when a form is closed/hidden via Me.Hide while a RefEdit has the focus, Excel will behave very funny. It is still in “RefEdit entry mode”, but no form is visible. My workaround for this is to set the focus to another control, e.g. one of the command buttons, before hiding the form.
For example:
Sub CB_Close_Click()
CB_Close.SetFocus
Me.Hide
End Sub
Matt says
My experience of global distribution (especially to non-English speaking countries) of tools containing the RefEdit control has lead me to stay as far away from this control as possible – the ‘Irregularities’ you mention can at least be worked around, but I’ve come across numerous others which I never managed to find solutions for (some of which seemed to be caused when the RefEdit control was used in conjunction with other native MS controls??). This was in Excel 2003 SP3.
You’ll be walking on fire here.. so be careful!
Jon Peltier says
Daniel –
I don’t think it’s possible to cover all of the funny behaviors of the RefEdit in a single post. It’s hard to even remember them.
Why doesn’t Excel need the RefEdit library reference? Who knows? If you remove the reference to MS Forms 2.0, you end up with compile errors. Go figure.
I’ve seen the issue you’ve described, and it reminds me very much of what happens when you put a RefEdit onto a modeless form. In my utilities I have to be mindful of this behavior; when the user clicks either the OK or Cancel button, the form is hidden, but the RefEdit may still think it’s active. Usually clicking on a button is enough to give focus to the button, but not always.
Jon Peltier says
Matt –
Relying on RefEdits can be tricky, but I’ve found that Detect and Repair makes most problems go away, and not only those related to the RefEdit.
Another thing I didn’t mention, but will be part of my ongoing Build an Excel Add-In series, is that running the add-in through an earlier version of Excel can also clean up a lot of problems. I keep a VM on hand with Office 2000. I load the add-in into Excel 2000, run Rob Bovey’s Code Cleaner (one of the handy add-ins from http://appspro.com/, and it’s free), then compile the code and save the add-in.
This is a good discussion. I think after more people have chimed in on their RefEdit experiences, I’ll rewrite this article with more details on symptoms and remedies.
Jeff Weir says
It’s a shame that RefEdit doesn’t allow you to select one cell in an array and then use Shift + Cntl + Down (or Up) and/or Shift + Cntl +Right (or Left) to select really long ranges rather than having to drag down and/or across with the mouse.
Daniel says
Jeff,
it does work. However you have to select two or more cells with the mouse; then the CTRL+SHIFT+arrow keys will do their job. I do it all the time.
Jeff Weir says
Cool! Thanks Daniel.
John Walkenbach says
“Out of the box, the Controls Toolbox does not have the RefEdit control.”
Are you sure about that. I don’t remember ever adding it to the Toolbox. I just checked a fresh installation of Excel 2010 Beta on a brand new system, and RefEdit was in the Toolbox.
Jon Peltier says
Daniel & Jeff –
Right here in 2003 SP3 I just did the CTRL+SHIFT+ARROW thing, with only one cell selected first, and it worked. I remember it not working in the past, maybe, but it looks fine now.
Jeff Weir says
Jon – I just tried the CTRL+SHIFT+ARROW thing, using the dialog in your LOESS utility, and it works. But when I tried it back in Sept 2009 I swear it didn’t work.
Maybe something changed with how this function works? I did load a new vista SP today…would that have affected Excel? Or maybe it always worked like this.
Jon Peltier says
John –
Now that you ask, I’m not sure why I stated that. Maybe I’m thinking of something else, or maybe in the old days it worked that way. I just checked by installing Office (2002 FWIW) on a VM. And the RefEdit was there.
I’ve removed this section of the post. I can reuse part of it in an upcoming article about forms controls.
Nicholas Hebb says
I use Application.InputBox(), but I’ve found it has quirks as well. I can’t remember off the top of my head, but I think it throws an error if the user clicks cancel. Also, I think the default range has to be passed as a Variant (which is never intuitive to me).
Jon Peltier says
Jan Karel Pieterse describes a problem and workaround with Application.InputBox in Getting a range from the user with VBA (Bug in Application.InputBox function). In Excel 95 through 2003, if the active sheet has conditional formatting using the Formula Is option, the InputBox method will fail. The bug was fixed in Excel 2007.
shohreh says
Hi
I visited ur site.
Sry I have a question,How I can enter about 4000 characters(about 2,000,000 cells) in Refedit?
I want to enter this text:
Sheet3!$C$1:$E$7062, Sheet3!$F$1:$H$7062, Sheet3!$I$1:$K$7062, Sheet3!$L$1:$N$7062, Sheet3!$O$1:$Q$7062, Sheet3!$R$1:$T$7062, Sheet3!$U$1:$W$7062, Sheet3!$X$1:$Z$7062, Sheet3!$AA$1:$AC$7062, Sheet3!$AD$1:$AF$7062, Sheet3!$AG$1:$AI$7062, Sheet3!$AJ$1:$AL$7062, Sheet3!$AM$1:$AO$7062, Sheet3!$AP$1:$AR$7062,…..
Best Regards
Jon Peltier says
Shohreh –
First, if you have this many discontiguous areas, you would be wise to combine them contiguously on another sheet, linking to the original discontiguous data.
Second, the multiple area range you’ve selected can be simplified to Sheet3!$C$1:$AR$7062, since there are no blank columns between the areas and all ranges are the same height in rows. Any reason to select the data this way?
Ryan Sermas says
Possibly old news, but I found this page when searching for ways to make the arrow keys work with RefEdit. I stumbled upon a fix from microsoft (http://support.microsoft.com/kb/291110) for this.
Regarding this: “Also, I’d like to add another pitfall using RefEdit controls. In my experience, when a form is closed/hidden via Me.Hide while a RefEdit has the focus, Excel will behave very funny. It is still in “RefEdit entry mode”, but no form is visible.” I have found that setting TabStop to False eliminates this behavior for me. Just by changing that one setting I’ve had virtually no problems with RefEdits, even in combination with Frames / Multipages, etc., nor have the few coworkers using the Addins. Not exactly a large sample size, but just wanted to share my experience.
Jon Peltier says
Ryan =
Thanks for your suggestions.