Range object InputBox problem (empty value)

Question

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:

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

Use proper error handling instead of ```on error resume next``` — Warcupine, Oct 12 '20 at 14:15
In addition to the answer below, I recommend to read: [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) • Never use `On Error Resume Next` without proper error handling. — Pᴇʜ, Oct 12 '20 at 14:18
Got it. What you're receiving is the expected behavior in Excel. Pressing Enter wouldn't allow you to continue. Error handling would apply for when user presses Cancel — Ricardo Diaz, Oct 12 '20 at 14:34

Pᴇʜ · Accepted Answer · 2020-10-13T06:09:49.700

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.

Oh well, I was hoping to prevent that, or actually replace with my own warning message, but I guess this is OK. I will for sure use that Cancel event :) — ch1zra, Oct 12 '20 at 17:43
@ch1zra you can turn of **all** message boxes that come from that InputBox, but then you don't get any warnings at all. Replacing of the message is not possible. See my edited answer. — Pᴇʜ, Oct 13 '20 at 06:10

score -1 · Answer 2 · answered Oct 12 '20 at 14:41

-1

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

answered Oct 12 '20 at 14:41

Apafey

66
7

Range object InputBox problem (empty value)

2 Answers2