Inputbox - Cell/Range selection - Nothing selected or Cancel

[Cosmos75]

I have the following code to ask the user to select a cell or range.

'Ask for selection
Set SelectedArea = Application.InputBox(prompt:="Select the cell/range...", _
                                      Title:="SWITCH CELL SELECTION", _
                                      Default:=Selection.Address, Type:=8)

How do handle it when the user clicks the Cancel button or doesn't select anything?


Also, is there any other way to tell if a single cell was selected or if a range was selected other than using

SelectedArea.Rows.Count
SelectedArea.Columns.Count

Thanks!

[Jacob Hilderbrand]

Try this:

Option Explicit
 
Sub Test()
Dim Selectedarea    As Range
Dim MyErr           As Long
On Error Resume Next
    Set Selectedarea = Application.InputBox(prompt:="Select the cell/range...", _
    Title:="SWITCH CELL SELECTION", Default:=Selection.Address, Type:=8)
    MyErr = Err
    On Error GoTo 0
    If MyErr <> 0 Then
        MsgBox "You press Cancel, this procedure will now terminate."
        Exit Sub
    End If
MsgBox Selectedarea.Cells.Count 'Change to do what you want if Cells.Count = 1
End Sub

[Cosmos75]

MyErr = Err
On Error GoTo 0
If MyErr <> 0 Then
MsgBox "You press Cancel, this procedure will now terminate."
Exit Sub
End If

This never executes when I click cancel?

Maybe I am missing something obvious or am just plain vanilla dumb, but what does On Error GoTo 0 do?

By adding a Dim Selectedarea As Range I do however raise a error # 424 (Object Required), whereas before I got error # 13 (Type Mismatch). Perhaps trapping for that error will be enough for when Cancel is clicked.

But when I leave the inputbox empty or enter some string, I see message (see the attached picture). No idea what's going on there....

[Daniel Klann]

Hi,

A small variation on DRJ's code:

Sub Test()
    Dim Selectedarea As Range
    On Error Resume Next
    Set Selectedarea = Application.InputBox(prompt:="Select the cell/range...", _
            Title:="SWITCH CELL SELECTION", Default:=Selection.Address, Type:=8)
    If Selectedarea Is Nothing Then
        MsgBox "You press Cancel, this procedure will now terminate."
        Exit Sub
    End If
    MsgBox Selectedarea.Cells.Count    'Change to do what you want if Cells.Count = 1
End Sub

HTH
Dan

[Jacob Hilderbrand]

I just double checked and the error does trigger when you press Cancel, so I am not sure what is happening for you.

When you Dim as Range make sure you use the Set statement to assign the Range

i.e.

Dim Rng1 as Range

Set Rng1 = ...

The picture you posted is the error that is built into the Application.Inputbox If you try to input invalid data you get the error. Since a blank "" is not a range you get the error when you leave this empty.

On Error Resume Next tells VBA to ignor errors
On Error Goto 0 tells VBA to reset what it does for errors and start handling them like it normally would.

[Cosmos75]

On Error Resume Next tells VBA to ignor errors
On Error Goto 0 tells VBA to reset what it does for errors and start handling them like it normally would.

Thanks! I did not know that you can use more than 1 "On Error" statements or even wondered what On Error Resume Next did!

Wonderful!

[Zack Barresse]

Personally, I'd go with Dan's idea, the Is Nothing, that's what you should use. You don't need to use the expensive On Error Resume Next. You need to be extremely careful with that, it will be hard to tell what error you are getting (especially in debugging your code). It makes a good catchall but should be used carefully.

[Jacob Hilderbrand]

Zack

Dan used On Error Resume Next as well, unless I am going blind.

[Zack Barresse]

Doh! I knew that, argh, too tired, shouldn't have posted. I meant to say that I prefer using On Error GoTo myDest types of statements. The Resume Next is the part I'm leary of, but with only one or two things to error out on, it's not bad at all. (Sorry)