This bug applies to all excel versions as from Excel 5/95. It has been fixed in Excel 2007.
This article describes a bug recently discovered by Ron de Bruin and also 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 up to and including 2003!). 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.
MsgBox "It appears as if you pressed cancel!"
There are two possible workarounds.
Method 1: use a userform.
I included a userform with two controls: A dropdown to select the workbook and a refedit control to select ranges. Note that the refedit control has been causing some havoc with some users, due to updates to Office versions. A foolproof way to overcome trouble which users might have with your file is opening the file in Excel 2000 and saving it again, before distributing.
The second method was suggested by Peter Thornton (also an Excel MVP).
It does use the Application.InputBox method, but uses type 0 instead of 8 and a helper function to extract a proper range from the entered string. Funny thing is, even though one uses type 0, the box still enables you to select cells.
The returned formula normally requires a little parsing before it will be recognized as a valid Range address. Therefore it makes sense to wrap all this in a function. This demo wrapper has two bonus features. Firstly, the developer has the option to re-activate the Input range. Secondly, if the user types an address instead of selecting cells, the user can make a couple of incorrect typo attempts without having to start all over, it happens!
' The other arguments are standard InputBox arguments.
sDefault = " first select a Worksheet"
sDefault = " Select Cell(s) or type address"
Both methods are demonstrated in this download.
Method 2 is working only within the same "Excel" - Application Object.
It's not possible to select a range from a second (running) MS Excel instance.
If you use InputBox with the Type 8 .. this is possible.
This is a good tool. I would like to use the selection to print a selection of the sheet.
I think i must replace "MsgBox "You selected:" & oRangeSelected.Address(, , , True)" with a print command.
Jan Karel I am really grateful for your work on this horrible issue. Has this bug really been fixed 2007 onwards? Unfortunately I have a task that might be opened in 2003.
At first I wanted to use Method 2 but then I found it didn't handle the selection of entire rows but I found your Method 1 did.
I am using a VBA macro to retrieve the contents of a status field in an Excel worksheet. If the status field contains a value less than 255 characters, it returns the entire status. When the status field contains 255 characters or more, it returns #VALUE!. How can I retrieve the entire status field?
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
This will tell you how to build the formula from code.
Just wanted to say thanks a million for an absolutely fantastic site and sharing your knowledge with the rest of us. I've learned a great deal from your site.