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.