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

User · Answer

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

User · Answer

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  quot Input custom range quot          quot Cancel-press test quot   Selection Address  Type  8  If StrPtr sRange    0 Then   I also tried with sRange address and vbNullString     MsgBox   quot Cancel pressed  quot       Exit Sub End If     MsgBox   quot Your custom range is  quot   amp  sRange Address  End Sub  The only thing that works  in this case  is an  quot On Error GoTo ErrorHandler quot  statement before the InputBox   ErrorHandler at the end  Sub Cancel Handler OK   On Error GoTo ErrorHandler Set sRange   Application InputBox  quot Input custom range quot          quot Cancel-press test quot   Selection Address  Type  8  MsgBox   quot Your custom range is  quot   amp  sRange Address  Exit Sub ErrorHandler      MsgBox   quot Cancel pressed quot   End Sub  So  the question is how to detect either an error or StrPtr   0 with an If statement

User · Answer

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    amp  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

[excel] How to detect if user select cancel InputBox VBA Excel

Examples related to excel

Examples related to vba