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

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?

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

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