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.
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
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.
Hi Dennis
If I have more information I post it here
Great Ron :)
Kind regards,
Dennis
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.
Thanks John. Not looking good at all.
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)
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”.
Hi Doug,
I’ll have a look!
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).
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.
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
Of course you were all correct guys. Fixed.
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?
Doug: Nice solution. I’ve implemented that in latest version.
Jan,
I’m glad it worked.
Great Job,
but this workaround does not allow to select a range from an other workbook…
is it possible to fix it ?
Thanks,
Hi all
This bug is fixed in Excel 2007
Finally! At least one bug fixed to try and balance the zillion new ones introduced .
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.