How to exit function if user cancels or doesn't enter anything into inputbox

Question

I have a code that inserts text into a newly inserted row after it asks for the user to input text into an InputBox. The issue I have is that if the user selects cancel on the InputBox, the code will still run and it will insert a new row and write FALSE into column E. If nothing is entered and I press OK, then it will exit the function correctly. However, how do I also make it so that it exits the function when the user presses cancel?

Public Function ContactAdd() As Long
Dim cF As Range
Dim Response As String

With Worksheets("Database").Range("B:B")
'Find the cell in column B containing value in combobox
Set cF = .Find(What:=Worksheets("Database").ComboBox1, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

'If cell in column B is found
If Not cF Is Nothing Then
    'Open inputbox
    Response = Application.InputBox("Enter contact name")
    'If inputbox is cancelled, exit function
    If Response = "" Then
        MsgBox "Invalid - Exiting"
        Exit Function
    Else
    'If entry to inputbox is made, insert row below the combobox value that was found and paste response into column E of the row that was inserted
    cF.Offset(1, 0).EntireRow.Insert
    ContactAdd = cF.Offset(1, 0).Row
    Worksheets("Database").Cells(ContactAdd, 5).Value = Response
    End If
    Exit Function
End If
End With

ContactAdd = 0
End Function

Private Sub InsertContact1_Click()
'Execute ContactAdd function when button is clicked
ContactAdd
End Sub

This part of my code is therefore incorrect and I'm not sure why... I've tried using vbNullString instead of "" as well which didn't help.

    If Response = "" Then
        MsgBox "Invalid - Exiting"
        Exit Function

cboden · Accepted Answer · 2015-12-03 11:08:51Z

2

the problem is, that Application.InputBox is not the same as the "normal" InputBox. So if you use:

Response = InputBox("Enter contact name")
'If inputbox is cancelled, exit function
If Response = "" Then
    MsgBox "Invalid - Exiting"
    Exit Function
End If

it would work. If you want to use Application.InputBox instead, you have to change the IF statement to:

If Response = False Then

but in that case you have to define Response as Variant.

answered Dec 3, 2015 at 11:08

cboden

8239 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andre · Accepted Answer · 2015-12-03 11:08:33Z

0

Application.InputBox() returns False on Cancel, while InputBox() returns "". See the online help.

Try this in the Immediate window:

? (InputBox("Click cancel") = "")

? (Application.InputBox("Click cancel") = False)

Both return True on Cancel.

answered Dec 3, 2015 at 11:08

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Collectives™ on Stack Overflow

How to exit function if user cancels or doesn't enter anything into inputbox

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related