How to detect if user select cancel InputBox VBA Excel

If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class…

EDIT to properly differentiate between empty string and cancel, according to this answer.

Your example

Private Sub test()
    Dim result As String
    result = InputBox("Enter Date MM/DD/YYY", "Date Confirmation", Now)
    If StrPtr(result) = 0 Then
        MsgBox ("User canceled!")
    ElseIf result = vbNullString Then
        MsgBox ("User didn't enter anything!")
    Else
        MsgBox ("User entered " & result)
    End If
End Sub

Would tell the user they canceled when they delete the default string, or they click cancel.

See http://msdn.microsoft.com/en-us/library/6z0ak68w(v=vs.90).aspx

Leave a Comment