InputBox User Clicked Cancel

[goss]

Hi all,

Using Excel 2013.

How do I trap and what do I return to the Sub when the user clicks Cancel on an Application.InputBox?
I am trying to get Range Object based on a cell the user clicked on.

The code currently returns "Object required.." error at Set rng = Application.InputBox( _

thx
w

Please Login or Register  to view this content.

[mehmetcik]

Could you not use a sheet specific macro?

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

Or just this...

Please Login or Register  to view this content.

[goss]

Thanks AlphaFrog,

Any other ideas?
At this point there is nothing on the worksheet, so nothing should be returned at all
I'm using rng = ws.cells(1,1) but that is not correct.

If the worksheet is truly empty, I should catch that and tell the user, not return a range.

thx
w

[AlphaFrog]

I don't follow what you're asking.
What has that got to do with clicking Cancel?

(I'm stepping away for a couple hours)

[LJMetzger]

Hi goss,

There are several ways to do what you want. When I use 'InputBox' with Type:=8, I like to return the address [rng.address(False,False)] as a string (without the $ signs) rather than the range. This gives me flexibility in processing when the user selects 'Cancel' or a range I don't want to use. I return a string to the calling routine such as 'Illegal Selection', or 'Cancel', that is processed by the calling routine. Of course the calling routine now has to convert the address to a range.

I hope this helps.

Lewis

[Leith Ross]

Hello goss,

Whenever a variable is Dimensioned, it is automatically set to default value. For a Range this is the special object Nothing. If the user chooses Cancel on your InputBox which is set to select a Range, the selected Range will be Nothing. All you need to do is trap the error and assign the variable rng as the function's return value. The choises are a user selected range or Nothing.

Please Login or Register  to view this content.

[goss]

Leith,

Perfect - thanks!

I let the Function return the Range as Nothing, then I tested for the case of nothing in the Sub().

thx
w