Input box Validation Type 8

Question

Dim UserXRange As Range

Set UserXRange = Application.InputBox("X Input Range", "X Input", "Sheet1!$A$1:$A$10", Type:=8)

As part of input validation for Application.InputBox Type 8

If the user selects cancel or closes the input box or enters anything that is not a range, then msgbox should notify the user to select a range.

I tried below lines. As expected, I get RTE 13 type mismatch

Set UserXRange = Application.InputBox("X Input Range", "X Input", "Sheet1!$A$1:$A$10", Type:=8) 

If UserXRange = False Then 

Exit Sub 

End If

So, I tried below lines. I get RTE 424 object required

Dim UserXRange As Range
Dim marray As String

Set UserXRange = Application.InputBox("X Input Range", "X Input", "Sheet1!$A$1:$A$10", Type:=8) 

marray = IsArray(UserXRange) 

If marray = "" Then 

Exit Sub 

End If

How to implement input validation for the above scenario?

Answer 1

Using Type:=8 you get a Range object or Nothing, where Nothing means the user want to abort the procedure.

Andreas.

Sub Test()
Dim UserXRange As Range
On Error Resume Next
Set UserXRange = Application.InputBox("X Input Range", "X Input", Selection.Address(0, 0), Type:=8)
If UserXRange Is Nothing Then
'Aborted
Exit Sub
End If
On Error GoTo 0
'Rest of your code here
End Sub

Answer 2

I tried Is Nothing as well before. It throwed RTE 424.

So, resume next and restore VBA’s default error handling is how we handle it.