How to Check if a User Clicked [Cancel] on an InputBox in VBA

Did you know that it's possible to distinguish between a user clicking "OK" on an InputBox versus "Cancel", even if they did not enter any text?

Consider the following sample code:

Sub InputBoxTest()
    Dim Result As String
    
    Result = InputBox("Leave this box blank")
    If StrPtr(Result) = 0 Then
        Debug.Print "User clicked [Cancel]"
    ElseIf Len(Result) = 0 Then
        Debug.Print "User clicked [OK]"
    Else
        Debug.Print "User can't follow instructions"
    End If
    
End Sub

Here is the test code in action:

If you're interested in why this works, I recommend the following question on stackoverflow: What are the benefits and risks of using StrPtr in VBA?  Both top-voted answers (from users Comintern and GSerg) are worth reading for background knowledge.

External references

What are the benefits and risks of using the StrPtr function in VBA?

While looking for a way to test when a user cancels an InputBox, I stumbled across the StrPtr function. I believe it checks if a variable was ever assigned a value and returns zero if it was never

Stack OverflowChrisB

Image by Ron van den Berg from Pixabay