CODEWITHSUNDEEP
excelvba
R4D4
R4D4

Reputation: 1402

Checking for user selected range or cancellation

I have the following code that prompts the user to select a range of cells.

I am having difficulty checking if the range is valid (i.e. did they just enter a number into the input box?) or if they pressed cancel. How can I differentiate?

Do While (True)    
    Dim mInput As Range
    mInput = Application.InputBox(Prompt:="Please select the cells.", Type:=8)
    If (mInput Is Nothing) Then
        Exit Sub
    End If

    If (mInputRange.Columns.Count <> 1) Then
        MsgBox "There must be only one column selected.", vbOKOnly
    Else

    End If

Loop

Upvotes: 2

Views: 1400

Answers (2)

brettdj
brettdj

Reputation: 55682

While mkingston's code does work (and I upvoted) I think it is preferable to test for Nothing then revert to an error handler. The code can then flow on to an action, or take another path, rather then need to resume post error handling (which I reserve for issues that can't be anticipated)

Sub test()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Please select the cells", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
    If rng.Columns.Count <> 1 Then
        MsgBox "There must be only one column selected.", vbOKOnly
    Else
        'do stuff
    End If
Else
    MsgBox "User either cancelled range selected or selected an invalid range", vbCritical
End If
End Sub

Upvotes: 5

mkingston
mkingston

Reputation: 2718

Excel raises an error before the result is returned if it cannot be converted into a range. Try doing it yourself.

Also, I presume you're already doing this but you'll need to use

set mInput = Application.InputBox(Prompt:="Please select the cells.", Type:=8)

instead of

mInput = Application.InputBox(Prompt:="Please select the cells.", Type:=8)

If you press cancel an error is raised also, meaning you need to use some sort of error checking routine:

Sub test()

    On Error GoTo handler

    Dim rng As Range

    Set rng = Application.InputBox("a", Type:=8)

    Debug.Print rng.Address

    Exit Sub

handler:
    Select Case Err.Number
        Case 424
            MsgBox "Cancelled.", vbOKOnly + vbInformation, "Cancellation dialog"
        Case Else
            Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End Select

End Sub

Upvotes: 3

Related Questions