How to detect if user select cancel InputBox VBA Excel

I have an input box asking user to enter a date. How do I let the program know to stop if the user click cancel or close the input dialog instead of press okay.

Something like if str=vbCancel then exit sub

Currently, user can hit OK or Cancel but the program still runs

str = InputBox(Prompt:="Enter Date MM/DD/YYY", _ Title:="Date Confirmation", Default:=Date)

8 Answers

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 (v=vs.90).aspx

Following example uses InputBox method to validate user entry to unhide sheets: Important thing here is to use wrap InputBox variable inside StrPtr so it could be compared to '0' when user chose to click 'x' icon on the InputBox.

Sub unhidesheet() Dim ws As Worksheet Dim pw As String pw = InputBox("Enter Password to Unhide Sheets:", "Unhide Data Sheets") If StrPtr(pw) = 0 Then Exit Sub ElseIf pw = NullString Then Exit Sub ElseIf pw = 123456 Then For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next End If End Sub 

The solution above does not work in all InputBox-Cancel cases. Most notably, it does not work if you have to InputBox a Range.

For example, try the following InputBox for defining a custom range ('sRange', type:=8, requires Set + Application.InputBox) and you will get an error upon pressing Cancel:

Sub Cancel_Handler_WRONG() Set sRange = Application.InputBox("Input custom range", _ "Cancel-press test", Selection.Address, Type:=8) If StrPtr(sRange) = 0 Then 'I also tried with sRange.address and vbNullString MsgBox ("Cancel pressed!") Exit Sub End If MsgBox ("Your custom range is " & sRange.Address) End Sub 

The only thing that works, in this case, is an "On Error GoTo ErrorHandler" statement before the InputBox + ErrorHandler at the end:

Sub Cancel_Handler_OK() On Error GoTo ErrorHandler Set sRange = Application.InputBox("Input custom range", _ "Cancel-press test", Selection.Address, Type:=8) MsgBox ("Your custom range is " & sRange.Address) Exit Sub ErrorHandler: MsgBox ("Cancel pressed") End Sub 

So, the question is how to detect either an error or StrPtr()=0 with an If statement?

If your input box is an array, it does not work. I have solved it by adding a check for if it is an array first.

Dim MyArrayCheck As String Dim MyPlateMapArray as variant MyPlateMapArray = Application.InputBox("Select ....", Type:=8) MyArrayCheck = IsArray(MyPlateMapArray) If MyArrayCheck = "False" Then Exit Sub End If 

I have solved it with a False like below

MyLLOQ = Application.InputBox("Type the LLOQ number...", Title:="LLOQ to be inserted in colored cells.", Type:=1) If MyLLOQ = False Then Exit Sub 

If user click cancel the sub will exit.

Another suggestion.

Create a message box when inputbox return null value. Example:

Dim PrC as string = MsgBox( _ "No data provided, do you want to cancel?", vbYesNo+vbQuestion, "Cancel?") 

Sub TestInputBox() Dim text As String text = InputBox("Type some text") If text = "" Then MsgBox "button cancel pressed or nothing typed" Else MsgBox text End If End Sub

Inputbox send a boolean False value when Cancel is pressed.

contenidoy = Application.InputBox("Cantidad = ", titulox, contenidox, , , , , Type:=1)

'ESC or CANCEL

If contenidoy = False Then MsgBox "Cancelado" Else MsgBox "Edición aceptada" 'End If