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.
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 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 Sub
The 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 Boolean
When 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 Sub
We 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 Sub
The 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 Sub
The 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 Property
When 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 Sub
Me.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 Sub
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.
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%.