How to detect if user select cancel InputBox VBA Excel

Question

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)

Answer 1

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

Answer 2

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