Bug in Application.InputBox function

This article describes a bug recently discovered by Ron de Bruin and which has also
been reported here.

The Application.InputBox function is very useful to get a range from
the user. Unfortunately, this function exposes a bug in Excel (all current
versions!). If the sheet on which a (range of) cell(s) is selected contains
conditional formatting using the : “Formula Is” option, the function may fail,
returning an empty range.

The only reliable workaround is to build a userform to request the range from
the user, which I have included as a download here.

Posted in Uncategorized

20 thoughts on “Bug in Application.InputBox function

  1. J-K,

    Will MSFT consider it when they release next SP for (at least) 2003?
    Does it still exist in the present beta version of Excel 2007?

    Kind regards,
    Dennis

  2. Well, Ron de Bruin said he would issue a bug, so they’ll be informed.

    Up to Ron to get back here to report any feedback he gets…

    Excel 2007 exposes the same behaviour.

  3. Actually, I think it’s even worse with Excel 2007, which has lots of new conditional formatting options. It appears that all of these new options causes the problem with InputBox. In other words, the user doesn’t even have to create a “Formula Is” condition.

  4. Jan,

    (I tried to post this comment on your site, but got “action not allowed” message)

    I downloaded your userform and noticed that if I exit it by hitting “x” I get this error:
    “The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed”

    In your QueryClose module, if I add this it works:
    If CloseMode = vbFormControlMenu Then Cancel = True

    (Credit to PED, of course)

  5. Jan,

    One other thing. Now if I click “x” the range selection flashing lines remain around the last range selected. I have to select another range and then hit Esc before I get the “you cancelled” msgbox. In the debugger it hangs on “.Show”.

  6. I just checked. With the version currently on my site I cannot repro your problem. Both the cancel and the cross produce a “You pressed cancel” message. (Excel XP).

  7. I meant to tell you, I’m using XL 03. However, I tried it on another computer using XP and got the same problems – both #7 and #8 above. Hopefully it’s just me.

  8. If CloseMode vbFormCode Then
    MsgBox “Use the Cancel button to close the form.”, _
    vbOKOnly, “????”
    Cancel = True
    Else
    cmbCancel_Click
    End If

    If I use this it is working for me

  9. Jan,

    I found a solution to #8 above. I thought it might have to do with the refedit control, which I know is sometimes problematical, so I set the focus to cmdCancel. It fixed the problem I was having.

    Also, I noticed that your current version does nothing when the “x” is clicked. So here’s my solution to both of those:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode vbFormCode Then
    Cancel = True
    Me.cmbCancel.SetFocus
    Call cmbCancel_Click
    End If
    End Sub

    Does this work for you?

  10. Great Job,
    but this workaround does not allow to select a range from an other workbook…
    is it possible to fix it ?

    Thanks,

  11. To balance the dozens of charting bugs and inconsistencies, Microsoft has now made it possible to define log scale endpoints that are not constrained to powers of ten. One step forward, tex steps back.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *