Alternative to Excel’s Flaky RefEdit Control

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.

System Error &H80004005 (-2147467259). Unspecified error

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:

Dialog with RefEdit

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.

Dialog with RefEdit, Contracted

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:

Dialog with TextBox

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.

Drop Button Styles

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:

InputBox input types

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:

Dialog with TextBox Drop Button

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:

InputBox for Range Selection

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 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

Sample 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%.

On Error Resume Next

Peltier Tech Chart Utility

Comments

  1. 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

  2. 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.

  3. 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

  4. 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.

  5. 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

  6. Tushar -

    Very interesting approach. I like its modeless nature. I’ll have to experiment with it.

  7. 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.

  8. 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.

    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)
    
    ' old code:
    '  If IsRange(sAddress) Then
    '    Set rng = Range(sAddress)
    '  Else
    '    sAddress = Application.ConvertFormula(sAddress, xlR1C1, xlA1)
    '    If IsRange(sAddress) Then
    '      Set rng = Range(sAddress)
    '    End If
    '  End If
    ' end of old code
    
      ' workaround
      On Error Resume Next
      sAddress = Application.ConvertFormula(sAddress, xlR1C1, xlA1)
    
      If IsRange(sAddress) Then
        Set rng = Range(sAddress)
      End If
      ' end of workaround
    
      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

    I hope it works for everyone.

    Regards,

    Julien

  9. 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.

  10. 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?

  11. 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.

  12. Thanks! That would be great. I will attempt a function. This blog is great.

  13. 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 Explicit

    Private 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

  14. It doesn’t work with merged cells.

  15. 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.

  16. 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!

  17. 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?

  18. 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.

  19. 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

  20. 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.).

  21. 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.

  22. 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.

Subscribe without commenting

Trackbacks

  1. [...] formulas. It basically does what I did manually. I started with RefEdit controls, but switched to PeltierTech’s alternative to RefEdits on this form. Did you know that you can’t use Control+Tab to switch workbooks with a [...]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites