Range object InputBox problem (empty value)

user_1096859 0
Viewed 458

I have a macro that requires user to select range, and then it carries on:

Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select a cell", "Make it happen!", Type:=8)

And when range is selected, everything works great. But when user just presses Enter without selecting any range, I get error:

There is a problem with this formula.

I tried to check rng.value, rng.count, but without any luck. I just want a simple Exit Sub if nothing is selected.

inputbox range vba excel
2 Answers

The following example shows how you would use the InputBox properly to select a range. It will throw a message box "user pressed cancel" or the range the user selected.

Option Explicit

Public Sub InputBoxExample()
    Dim rng As Range
    
    On Error Resume Next
    Set rng = Application.InputBox("Select a cell", "Make it happen!", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then
        MsgBox "user pressed cancel"
        Exit Sub
    Else
        MsgBox "user selected " & rng.Address, False, False
    End If

End Sub

If you press OK without selecting anything you get the message that you showed in your question. This is a behavior of the InputBox and the message is not from VBA, therefore it cannot be changed. Actually the reason of the message is that the user is forced to either enter a valid address (or formula that resolves in an address) or press cancel to abort selecting.

What you actually can do is supressing all alerts of that box. But not the one specifically.

Application.DisplayAlerts = False
On Error Resume Next
Set rng = Application.InputBox("Select a cell", "Make it happen!", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True

This will turn all alerts off that come from that InputBox. So if you choose anything invalid (or nothing) the OK button will just do nothing until you have a valid address in the box or press Cancel.

user_32196130

A possible modification to the suggested function:

Public Function IsAddressValid(myAddress As String) As Boolean
    On Local Error Resume Next
    IsAddressValid = Range(myAddress).Rows.Count > 0
End Function
user_144310200
Related