Handle Cancellation on input box (Range)

[SkywardPalm]

I am having trouble handling the cancellation of an input box that pops up for this macro. Currently, if I press cancel it will continue Next instead of Exit Sub. I am not sure what the issue is but I assume it's due to the range input box.. The second input box cancels fine.

Public Sub RangeInputBox()

Dim rg As Range

On Error Resume Next
Set rg = Application.Selection
Set rg = Application.InputBox(Title:="Merge Cells", Default:=rg.Address, _
    Prompt:="Select your range:", Type:=8)
Err.Clear

'to ensure User Did not cancel
'below line causes OK to Exit Sub instead of move Next
'If StrPtr(rg) = 0 Then Exit Sub
If rg Is Nothing Then Exit Sub
On Error GoTo 0

Separator = Application.InputBox("Separate values with:", Title:="Merge Cells", Default:=" ")
If Separator = False Then
    Debug.Print "User Canceled!"
    Exit Sub
End If
End Sub

 

[Joe4]

Instead of ignoring the error with a "Resume Next" statement, try using error handling instead, something like this:

Public Sub RangeInputBox()

Dim rg As Range

On Error GoTo err_chk
Set rg = Application.Selection
Set rg = Application.InputBox(Title:="Merge Cells", Default:=rg.Address, _
    Prompt:="Select your range:", Type:=8)

'to ensure User Did not cancel
'below line causes OK to Exit Sub instead of move Next
'If StrPtr(rg) = 0 Then Exit Sub
If rg Is Nothing Then Exit Sub


Separator = Application.InputBox("Separate values with:", Title:="Merge Cells", Default:=" ")
If Separator = False Then
    Debug.Print "User Canceled!"
    Exit Sub
End If

Exit Sub


err_chk:
    If Err.Number = 424 Then
        MsgBox "Process cancelled", vbOKOnly
    Else
        MsgBox Err.Number & ":" & Err.Description, vbOKOnly
    End If

End Sub