I recently wrote about Using RefEdit Controls in Excel Dialogs. I use(d) them a lot, and they are a handy way on a dialog to get a range that the user wants a procedure to use.
These controls are prone to incompatibility issues between versions of Excel, particularly between Classic Excel (up to 2003) and new Excel (2007 and later), and whenever there’s a new security update from Office update, these problems seem to arise again. I’ve written about this and related problems in Unspecified but Painfully Frustrating Error. This error has led to an inordinate amount of customer support to update customer installations and correct the errors.
To improve the user experience of my customers, and to cut down on customer support incidents, I’ve replaced the RefEdit controls in all of my commercial utilities with an alternative method, as I’ve discussed in Charting Utility Upgrades. This article describes the replacement for the RefEdit functionality.
RefEdit
A typical dialog using a RefEdit looks like this:
Clicking in the RefEdit highlights the indicated range. The user can edit the text within the RefEdit, and the highlighted range updates.
Clicking in the RefEdit’s drop box, or selecting a range in the sheet contracts the dialog so that only the refEdit is visible, and the indicated range updates as the user changes the selection.
TextBox and Inputbox
The new approach uses a TextBox in the dialog instead of the RefEdit, which at first doesn’t look like it’s going to work:
We can use a couple invisible properties of a TextBox to provide a drop button like that in the RefEdit, so it looks exactly the same in the dialog. I call the properties “hidden” because they do not appear in the TextBox property window in the VB Editor, though they appear for example in the ComboBox property window. Fortunately you can type the properties and they work. One hidden property is TextBox.DropButtonStyle which sets the style of the drop button (see below); we use the “Reduce” style to match the RefEdit. The other property is TextBox.ShowDropButtonWhen which controls the visibility of the drop button, and we write code that always shows the drop button.
The TextBox does not interact directly with a range as does the RefEdit. Instead, when this drop button is clicked, the dialog is temporarily hidden, and an InputBox appears to solicit the user’s input.
The InputBox control is familiar to Excel VBA programmers who use it to ask the user to enter a number or some text. The syntax of the InputBox is:
Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)Prompt is the text that instructs the user, Title is the text in the title bar of the dialog, Default is the value in the input box when the InputBox opens. Left and Top define the position of the InputBox (if unspecified it appears in the center of the window). HelpFile and HelpContextId are used with custom help topics. Finally, Type is used to define the expected type of input:
It may be tempting to use type 8 to indicate a range object, but there is an obscure glitch that causes it to fail when the worksheet contains conditional formatting conditions that use formulas in their definitions. Fortunately using Type 0 to specify a formula works just fine.
The dialog with the drop-button-enabled TextBox looks like this:
By itself the drop button has no function, but we can write code to respond to the user clicking on it. We have the dialog hide itself and show the following InputBox:
The InputBox is larger than the compact dialog that shows only the active RefEdit, but that’s a minor cosmetic difference. The largest drawback of this approach is that it doesn’t show the highlighted range when the user is editing the text in the TextBox. The advantage of course is the avoidance of incompatibilities between RefEdit and the user’s MS Office environment.
The Code
The following procedure, in a regular code module, determines the address of the selected range, loads the dialog, passes the range address to the dialog, and waits for the user to finish. If the user does not cancel the dialog, then the procedure selects the new range.
Sub ShowDialog()
Dim frmAlternativeRefEdit As FAlternativeRefEdit
Dim rRange As Range
Dim sRange As String
Dim bCanceled As Boolean
If ActiveSheet Is Nothing Then Exit Sub
If TypeName(Selection) = "Range" Then
Set rRange = Selection.Areas(1)
sRange = Selection.Parent.Name
If InStr(rRange.Address(, , , True), "'") > 0 Then
sRange = "'" & sRange & "'"
End If
sRange = sRange & "!"
sRange = sRange & rRange.Address
End If
Set frmAlternativeRefEdit = New FAlternativeRefEdit
With frmAlternativeRefEdit
.Address = sRange
.Show
' wait for user input
bCanceled = .Cancel
If Not bCanceled Then
sRange = .Address
End If
End With
Unload frmAlternativeRefEdit
Set frmAlternativeRefEdit = Nothing
If bCanceled Then GoTo ExitSub
If IsRange(sRange) Then
Application.Goto Range(sRange)
End If
ExitSub:
End SubThe UserForm (dialog) is named FAlternativeRefEdit. The following code all goes into the code module behind the UserForm.
At the top of the code module we need:
Option Explicit
Private mbCancel As BooleanWhen the dialog is initialized, the drop button style and visibility are set:
Private Sub UserForm_Initialize()
Me.txtRefChtData.DropButtonStyle = fmDropButtonStyleReduce
Me.txtRefChtData.ShowDropButtonWhen = fmShowDropButtonWhenAlways
End SubWe include this snippet to make sure closing the dialog using the big red X is the same as pressing the Cancel button:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
btnCancel_Click
End If
End SubThe Cancel and OK buttons run the following code:
Private Sub btnCancel_Click()
mbCancel = True
Me.Hide
End Sub
Private Sub btnOK_Click()
mbCancel = False
Me.Hide
End SubThe dialog gets the address from the calling procedure and passes back the new address using these properties:
Public Property Let Address(sAddress As String)
CheckAddress sAddress
End Property
Public Property Get Address() As String
Dim sAddress As String
sAddress = Me.txtRefChtData.Text
If IsRange(sAddress) Then
Address = sAddress
Else
sAddress = Application.ConvertFormula(sAddress, xlR1C1, xlA1)
If IsRange(sAddress) Then
Address = sAddress
End If
End If
End Property
Public Property Get Cancel() As Boolean
Cancel = mbCancel
End PropertyWhen the user clicks the DropButton on the TextBox, this code runs:
Private Sub txtRefChtData_DropButtonClick()
Dim var As Variant
Dim rng As Range
Dim sFullAddress As String
Dim sAddress As String
Me.Hide
On Error Resume Next
var = Application.InputBox("Select the range containing your data", _
"Select Chart Data", Me.txtRefChtData.Text, Me.Left + 2, _
Me.Top - 86, , , 0)
On Error GoTo 0
If TypeName(var) = "String" Then
CheckAddress CStr(var)
End If
Me.Show
End SubMe.Left + 2 and Me.Top - 86 are empirically derived positioning values that align the InputBox to the dialog that called it. They were not rigorously determined and may not work the same for all settings in all versions of Windows.
Finally, this procedure parses the address in the InputBox, making sure the format (xlA1 or xlR1C1) is appropriate, and also ensuring that the user can select a range on a different worksheet. I’ve incorporated the suggestion made by Julien in the comments, who found that my original CheckAddress routine gave the wrong address if an entire column or row was selected.
Private Sub CheckAddress(sAddress As String)
Dim rng As Range
Dim sFullAddress As String
If Left$(sAddress, 1) = "=" Then sAddress = Mid$(sAddress, 2, 256)
If Left$(sAddress, 1) = Chr(34) Then sAddress = Mid$(sAddress, 2, 255)
If Right$(sAddress, 1) = Chr(34) Then sAddress = Left$(sAddress, Len(sAddress) - 1)
On Error Resume Next
sAddress = Application.ConvertFormula(sAddress, xlR1C1, xlA1)
If IsRange(sAddress) Then
Set rng = Range(sAddress)
End If
If Not rng Is Nothing Then
sFullAddress = rng.Address(, , Application.ReferenceStyle, True)
If Left$(sFullAddress, 1) = "'" Then
sAddress = "'"
Else
sAddress = ""
End If
sAddress = sAddress & Mid$(sFullAddress, InStr(sFullAddress, "]") + 1)
rng.Parent.Activate
Me.txtRefChtData.Text = sAddress
End If
End SubSample Workbook
RefEdit_Alternative.zip is a zipped workbook that contains the dialog and code featured in this post. Click on the button on the first worksheet to see it in action.
Summary
Using a TextBox and InputBox is an effective replacement for Excel’s RefEdit control.It may not be quite as elegant as the RefEdit, when the RefEdit is working. However, the TextBox/InputBox combination does not suffer from incompatibilities between different systems and Excel versions. Since I have converted my professional utilities to this alternative system, my customer support incidents have been reduced by 90%.
Dennis Wallentin says
Jon,
[quote]
my customer support incidents have been reduced by 90%
[\quote]
I believe that says a lot how poor the RefEdit control actually is and has always been. I think You’ve made a great work with the approach You present here. My only concern is the use of undocumented features as they can be removed in any future release without any notification.
Nice Work!
Dennis
Jon Peltier says
Dennis –
While others have experienced problems with RefEdits in the past, I had no problems to speak of until the release of Excel 2007. The 2007 version of the RefEdit library is different than all previous versions, both in version number and in file size, and it seems the RefEdit is now updated whenever a new Office update is distributed.
After 2007 came out, I developed a set of steps I had to take before deploying an add-in, and for a while these steps seemed sufficient to avoid problems. But the set of steps has become larger and more intrusive. I would have to ensure that the user ran Detect and Repair (or Recovery) and installed all of the latest updates, and often users do not have permissions that allow this. I could no longer afford to support RefEdits.
I’m not too concerned about the hidden properties. If they are deprecated, I can revert to an older version of this trick, which used a small square icon next to the text box instead of the drop button.
XL-Dennis says
Jon,
Oddly, I had to update my commercial (VBA based) tool due to some security updates last year in all the versions. No support call explicit targeting the RefEdit control but I guess that people who had issues never managed to get so far as to the tool’s UI. At that time I replaced the RefEdit control as well due to problems in all versions except 2K.
Kind regards,
Dennis
Jaafar tribak says
Nice work Jon!
One thing that puzzles me is that , contrary to what you stated, when I edit the text within a RefEdit control, the highlighted range doesn’t seem to update ! I am not sure if that’s because i am using XL2003.
Anyway.Your idea is great. Unfortunatly, the user still has to use an intermediary Excel InputBox which , in my humble opinion, kind of spoils most of the fun.
I have tried to take your idea a step further and I managed to come up with this :
http://www.mrexcel.com/forum/showthread.php?t=465512
There are still a couple of little issues with my approach namely :
1- The little clickbox to the right of the edit box needs a little cosmetic touch.
2- The highlighted range doesn’t update when editing the textbox.
I ‘ll give these 2 pending issues a shot and see if i can solve them.
Note:
Only tested on Excel 2003 under Win XP so I am not sure if it will work in other versions too.
I hope you find this interesting.
Tushar Mehta says
Hi Jon,
Have you seen the approach I have taken for a while now?
Range references in a userform
http://www.tushar-mehta.com/publish_train/book_vba/09_userinterface.htm#Range_references_in_a_userform
Jon Peltier says
Tushar –
Very interesting approach. I like its modeless nature. I’ll have to experiment with it.
Diego Klabjan says
It works like a charm under Office 2007 and vs 2008.
A few days ago I upgraded to Office 2010 and vs 2010 (both 64 bit). I have recompiled the refedit project and was able to use it in my Excel VSTO project (I had to make a few changes).
The unfortunate part is that I can open the form, the refEdit control is displayed correctly, however upon selecting a range, the selection is not displayed in the control. I”m not an expert with controls and thus it would take me a while to figure out what is going on.
Julien says
Hi Jon,
really nice job!
However, on my XL2003, I encounter the following issue.
It looks like the Sub CheckAddress(sAddress As String) sub doesn’t work properly when the user select a whole column.
If you select for instance column “B:B”, sAddress will be returned as C2.
Actually, the Inputbox method will return the value “=C2”, the xlR1C1 of “B:B”. But when treated in CheckAddress, “=C2” becomes “C2” and then can be seen as a valid adress for IsRange and therefore ConvertFormula is not ran.
Therefore I have written the following workaround.
I hope it works for everyone.
Regards,
Julien
Jon Peltier says
Thanks, Julien. I hadn’t selected an entire row or column, so I didn’t notice this problem. Clever workaround.
I’ve incorporated your change into the routine in my article, and now I have to deploy it in a lot of my projects.
Travis says
Question:
I am a very novice VBA programmer and require a userform with several refedits. Is there a way to modify this code to easily allow several of these refeditalternatives to run
through the same property let get and checkaddress codes through some sort of call? Can I somehow make the Me.txtRefChtData.Text line vary based on which txt box I’m using at the time or do i need include all the code with different variable names for each refeditalternatives box that i use?
Jon Peltier says
Travis –
Actually, it would be better to change CheckAddress to a function, returning True if the address is valid, false otherwise. The “Me.txtRefChtData.Text = sAddress” line should be removed from this function.
The calling procedure, in this case “txtRefChtData_DropButtonClick()” (or actually, one of these subs per faux RefEdit), would take the returned sAddress and apply it to the textbox. Much of the code in this event procedure could be removed to a single procedure that does the input box and returns the address. When I get a chance I’ll adjust my code here.
Travis says
Thanks! That would be great. I will attempt a function. This blog is great.
KL says
Hi Jon,
Thank you very much for this approach. I am trying to implement an alternative to Refedit in VB6 (just converting my VBA projects to COM addins) and your solution seems the cleanest option to me. I don’t seem to get the Type:=0 version to work in VB6 – there seem to be issues with showing the inputbox dialog and writing the result to the textbox (already mentioned here). However, Type:=8 does the job (and yes, I am aware of the glitches, but my user is likely to work with an unformatted dataset.). That said, if I were to use your approach in Excel, I would probably take a bit more restrictive approach by not allowing the user to edit the textbox. So I would simply place a label mask over the textbox and the following code in the UserForm module should suffice:
Option ExplicitPrivate Sub lbMask_Click()
Call GetRange
End Sub
Private Sub UserForm_Initialize()
reRange.DropButtonStyle = fmDropButtonStyleEllipsis
reRange.ShowDropButtonWhen = fmShowDropButtonWhenAlways
End Sub
Private Function IsRange(ByVal strAddress As String) As Boolean
Dim TestRange As Range
IsRange = True
On Error Resume Next
Set TestRange = Range(CleanAddress(strAddress))
If Err.Number 0 Then
IsRange = False
End If
Err.Clear
On Error GoTo 0
Set TestRange = Nothing
End Function
Private Function CleanAddress(ByVal strAddress As String) As String
Dim strTemp As String
strTemp = strAddress
If Left$(strTemp, 1) = "=" Then strTemp = Mid$(strTemp, 2, 256)
strTemp = RTrim$(LTrim$(strTemp))
On Error Resume Next
strTemp = Application.ConvertFormula(strTemp, xlR1C1, xlA1)
CleanAddress = strTemp
End Function
Private Sub GetRange()
Dim UserInput As Variant
Me.Hide
With Application
UserInput = .InputBox("", "", reRange.Text, Me.Left, Me.Top, , , 0)
If VarType(UserInput) = 8 And IsRange(UserInput) Then
reRange.Text = .ConvertFormula(UserInput, xlR1C1, .ReferenceStyle)
End If
End With
Me.Show
End Sub
Aldo D says
It doesn’t work with merged cells.
Jon Peltier says
Aldo –
A lot of things don’t work with merged cells. If at all possible, you should avoid merged cells on sheets you’re manipulating data on. Use merged cells only on display sheets.
Dave says
I have been struggling with the bugs in refedit for over 40 hours, and after finding your very elegant, comprehensive solutions I feel complete relief! Thank you for your brilliance!
Scott says
Jon-
One of the things I noticed and can’t find any info on is how the Application.Input works for a type 8. When I use this type of inputbox (called from a routine in a standard module), the type 8 usually allows me to select ranges based on keyboard use (i.e. end+down, ctrl+home, etc.)
When I use your method above, the same type of inputbox does not allow that. I thought maybe this was due to the variable type, so I changed it from a variant to a range, but that still did not work.
Do you know of some kind of limitation on how the type 8 inputbox functions when called from a form, rather than a routine in a modules?
Jon Peltier says
Scott –
I don’t recall the details, but there are cases in which type 8 causes problems.
Type 8 returns a range. Type 0 returns a formula, or really, a string. I use a variant to capture the Type 0 returned value, so I can test its type. A string means a valid entry, a boolean (False) means the user canceled. I also use On Error Resume Next before the statement that displays the InputBox, just in case.
There should be no difference in how the InputBox works whether called from a regular module or from a UserForm.
Dutch says
Thanks for the example, works as advertised, and I’m looking forward to use the technology in my projects.
I have however found a quirk in the internal “refedit” called by Excel [2010] with InputBox(), and occurs when selecting multiple cells using the [Ctrl] button. When you click the same cell another time, the reference is copied again in the InputBox. Note: this same error is also present in REFEDIT.DLL so I presume the issues is somehow related.
For example, this is what you get when selecting holding the [Ctrl] key down in sequence: [A1], [A2], [A3] and again [A2]: “=$A$1,$A$2,$A$3,$A$2”
Cell [A2] will now appear twice, while it is actually *UN*selected on the sheet. If not properly handled this can cause serious misinterpretation of the user’s decisions in your VBA code and should be properly handled by “CheckAddress”. I will see if in the mean time I can come up with a sleek solution and post it back into this thread.
Dutch
Jon Peltier says
Dutch –
In fact, if you ctrl-click a cell a second time, you are not deselecting the cell, you are selecting it twice. If you look carefully, the cell may look unselected, because the active cell is not shaded despite being selected.
In 2013, this behavior is changed, and a cell which is selected a second time is a darker shade of gray (and a third time, even darker, etc.).
Dutch says
You are right when the selection is done with XL idle, that is, in interactive mode, but the “refedit” style selection of cells matter of this post is different since the visual marker is implemented differently and uses the dotted line instead on cell shading.
As you say, in 2013 the behaviour has indeed changed (I presume because the developers in Redmond didn’t know how to implement this correctly) and the dotted line remains if you click twice. But in 2010 and lower the dotted line is removed if you click a second time on a cell.
Now, my users expect that when the dotted line is NOT there then the cells shall also not be considered, and I have received quite a few reports on this issue from them. I can always fall back on blaming M$ but my users finally establish what my application must do.
Jon Peltier says
If you look carefully, you see some flashing around the doubly-selected cell. It is as if the first selection leads to the dashed line highlight, the second selection (because the cell is selected again) puts the dashes on top of the first set, but makes them all disappear. A third selection shows the dashed line again.
You should precedence to the address in the range selection box, if it shows cells that don’t seem to be selected. It is impossible to ctrl-click to unselect part of a selected range.
Zack Barresse says
Finally had an opportunity to implement this into an actual project. Very slick. I know this is an old post, but it works a treat. Thanks Jon!
I did change the CheckAddress() function just a tad, to include someone typing in a range manually (trying to cover my respective user bases here).
I also noticed the IsRange() function wasn’t in the blog post itself. Glad I scrolled down!
Mike L says
This works well! Thanks for the post!
TungSon Nguyen says
Hi Peltier,
During looking a way to resolve the issue with RefEdit control, I got to your blog suddently.
I followed your works and You have been saving me a lot of work.
What I can do now is getting the Address from the workbook with the code in it.
Is there any way to get the address from another workbook ?
Thank you and best regards.
Jon Peltier says
Nguyen –
When the InputBox is deployed, you can navigate among open workbooks using Switch Windows on the View tab of Excel’s ribbon.
Andrija Vrcan says
Thank you Jon!
Great tip!