RefEdit controls are used throughout Excel to allow you to select a range from a dialog or box, or just type the range in. You’ve certainly used them even if you didn’t know what they were called.
RefEdit controls have an enter mode and an edit mode. In enter mode, the arrow keys select cells on the worksheet and adjust the range in the RefEdit. In edit mode, the arrow keys navigate through the text in the RefEdit, rather than select cells.
The RefersTo box in the Define Name dialog (Insert>Name>Define) is a RefEdit. I’ll use that as an example because it happens to be the one I screw up the most. Here’s the dialog with a dynamic range definition in it.
By default, this RefEdit is in enter mode. When I place my cursor at the end of the text and press the left arrow key, I get this:
The F2 function key puts the RefEdit in edit mode. If I start over, put my cursor at the end of the text, then press F2, then press the left arrow key, I get this:
Note how the cursor has moved to the right of 1 instead of selecting cells. For some reason, I have a mental block and always forget to press F2 first.
You’re not the only one who forgets the ‘F2’ which is why I’ve always tried to avoid using RefEdit when I develop stuff for other people. Why hasn’t the control got a property that you could set to put it in edit mode or something else that didn’t involve ‘Send Keys’. Does anyone know something I don’t?
I agree with you two. I think that it should be in Edit mode by default. If I’m going to select cells for a RefEdit control, I’ll use my mouse. It’s counter-intuitive to have the navigation keys select cells on the sheet that is, essentially, behind the active form.
“I’ve always tried to avoid using RefEdit when I develop stuff for other people”
I avoid them because the RefEdit is an extremely buggy control!
I avoid them because the RefEdit is an extremely buggy control!
How so?
I am using a RefEdit control object in a User Form. I am interested in learning about all of the RefEdit properties, methods and events. Where would I be able to find a comprehensive list of these and a detailed description of them?
I want to know how to have a particular worksheet displayed when the RefEdit button that collapses the user form is clicked, and return to the original worksheet when the button that restores the user form. What are these events? If I knew that I could program these events.
Please help me if you can.
Jim
When using refedits ALWAYS check that the registry contains following entry under:
HKCUsoftwaremicrosoftoffice11.0Exceloptions
QFE_Richmond = 1
(See KB291110 XL2000: Cannot Use Keyboard Shortcuts to Select Ranges in RefEdit Control.
KB mentions this as a bug for xl2000 but the option is still NOT set by default in xl2003. Just add it to all installed versions and you should be fine.
Still looking for extensive documentation on the RefEdit rascal…
I hate the redefit control, I have never once got it to work properly.
I hate the redefit control, I have never once got it to work properly.
RefEdits are handy little things, if you remember a few simple rules, and don’t try to get too fancy.
Always put RefEdits on the form itself, never on a control on a form. Don’t put them on a multi page or in a frame, unless you like to watch Excel hang. You can fake a frame with a couple labels, one blank with a border, the other with the caption.
Don’t get too fancy with events, because they are not reliable. Try to trap other controls getting or losing focus, to see if the RefEdit has changed.
For simple cases, use the InputBox, Type:=8 for range selection.
– Jon
Actually, refedit is working quite well for me, even in frames. A must use for me because with a simple inputbox, the entire address (i.e. sheet.name and range.address) is NOT captured, although when in the selection mode it fools you to think it is being captured as the entire path of sheet name and range address is displayed. So for selection of ranges on different sheets within the workbox, this seems to be the control to use.
No real documentation on all of refedit’s properties found by me, either. Does anyone know how to set the title bar when one is actually selecting a range in a refedit box? I’ve tried setting every single property available but with no luck…………….
I know this is an old thread, but..
I also have found the RefEdit to be buggy and unpredictable, particularly when encased inside another control (as mentioned above). This is actually mentioned in the MSDN knowledgebase, but only in the context of XL2003 if I remember correctly.
When sitting on the userform itself, so far no problems. Trying to figure out the sequence of events is a pain though….
– Phil
I quit using refedit altogether (xl2000) as it likes to hang things up. I use Application.InputBox(,,Type:=8)
Mike says:
“…the entire address (i.e. sheet.name and range.address) is NOT captured…”
Interestingly, if you use Immediate View and have a variable oRange that was Set oRange = Application.InputBox(Prompt:= “MyPrompt”, Title:=”MyTilte”, Type:=8) and type ?oRange.Address the entire address, including sheet name appears. However, when trying to extract sheet name anywhere else – it doesn’t make the trip. ‘Sup with that?
I guess oRange.Parent then should give you the sheet object tied to the selected cells.
“I guess oRange.Parent then should give you the sheet object tied to the selected cells.”
oRange.Parent produces the following error:
“Object doesn’t support this property or method”
Works for me:
Immediate Window:
?Application.InputBox(Prompt:= “MyPrompt”, Title:=”MyTitle”, Type:=8).address
$P$18:$S$32
?Application.InputBox(Prompt:= MyPrompt, Title:=MyTitle, Type:=8).address(external:=true)
[Book1]Sheet1!$P$18:$S$32
Sample procedure:
Sub TestRange()
Dim oRange As Excel.Range
Set oRange = Application.InputBox(Prompt:=”MyPrompt”, Title:=”MyTitle”, Type:=8)
Debug.Print oRange.Address
Debug.Print oRange.Address(external:=True)
End Sub
Procedure output:
$P$18:$S$32
[Book1]Sheet1!$P$18:$S$32
And this gives the expected result too:
Sub TestRange()
Dim oRange As Excel.Range
Set oRange = Application.InputBox(Prompt:=”MyPrompt”, Title:=”MyTitle”, Type:=8)
Debug.print oRange.Parent.Name
End Sub
Result:
Sheet1
What’s in a .Name? oRange.Parent.Name does the trick. Thanks
Also, giving complete library data when dimensioning variables is advised. But one must be carefull using Edit|Replace. EG. Replace Range with Excel.Range for the “entire project” will give some unexpected results. For me it was
oRange = oExcel.RangeActivesheet.UsedRange = Activesheet.UsedExcel.Range
It’s a fairly large project – now the cleanup.
Doco –
Maybe changing your search criterion would help…
Find:
As Range
Replace:
As Excel.Range
This eliminates most of the places where Range exists in a VBA statement, such as .UsedRange or .Range(“A1?)
– Jon
Right On! ;-)
Has anyone determined what properties can be used to set the title bar label in the rollup mode or how to change that ugly dash icon in the form mode (How do u set icon to look like forms in Excel)Using VBA