How to trap Cancel on Application.InputBox?

[dplum]

The following code generates an error on the InputBox command if Cancel is Clicked.

What is needed to allow the Cancel to be clicked and exit the Sub if it was clicked.

I'm checking for "False" but the program fails on the Input Command just before this check.

Sub NameSelectedCells()
    Dim vInputCells, vRefersto_Cell_Range As Range
    'type:=8 for cell references or ranges; change the Left and Top arguments to position the InputBox
    Set vInputCells = Application.InputBox(prompt:="Select cells to be named...", Left:=1, Top:=1, Type:=8)
    If vInputCells = False Then Exit Sub
    vSheet_Range = "'" & ActiveSheet.Name & "'!" & vInputCells.Address(ReferenceStyle:=xlA1)
    Set vRefersto_Cell_Range = Range(vSheet_Range)
    Do
        vRangeName = InputBox("Assign the Range[" & vSheet_Range & "]" & vbCr & _
                              "to the following Name...")
        If vRangeName = "" Then Exit Sub
        On Error Resume Next
        Names.Add Name:=vRangeName, RefersTo:=vRefersto_Cell_Range, Visible:=True
        If Err.Number = 0 Then Exit Sub
        MsgBox "RangeName... [" & vRangeName & "] is Invalid!" & vbCr & _
            "Must Not start with a Number, Must Not contain a Space, Only underscore (_) special character is allowed"
    Loop
End Sub

 

[poolhall]

If vInputCells Is Nothing Then Exit Sub

 

[dplum]

If vInputCells Is Nothing Then Exit Sub

But, I still get a run-time error when the Cancel button on the Inputbox is clicked -- any ideas why this happening, or how to avoid the error?

 

[poolhall]

yes, add:

On Error Resume Next

in the beginning of your macro.

 

[shg]

One way:

    Dim rInp As Range
    
    On Error Resume Next
    Set rInp = Application.InputBox(prompt:="Select cells to be named...", Left:=1, Top:=1, Type:=8)
    If Err.Number Then Exit Sub
    On Error GoTo 0

    ' carry on ...

 

[dplum]

shg,

Thx... but I find this strange.

The normal "Inputbox" command allows for the Cancel button to be clicked without generating an error.

Any idea why clicking Cancel on Application.Inputbox shoudl cause an error?

 

[shg]

Because when you press Cancel, the InputBox method (which is not the same as the InputBox function) doesn't return a range object to set the range variable to. It might more benignly return Nothing (like Find does when it doesn't find the sought value), but it doesn't.

 

[bensonsearch]

Because there is no error, it returns blank

need if statement after setting variable called asd

if asd = "" then
'code when cancel hit
Exit sub
else
'code when data entered
end if