Cancel with Input Box

[pugap]

Does anyone know how to handle Cancel with an Input Box, and distinguish it between an empty string when hitting Enter? I've seen code that looks like:

Sub TestInput()
Dim ans As String
ans = InputBox("Response:")
If StrPtr(ans) = 0 Then
MsgBox "You hit cancel"
Exit Sub
ElseIf ans = "" Then
MsgBox "You hit OK with no entry"
Exit Sub
Else
MsgBox "Your answer is " & ans
End If

but when I tried this, and strptr(ans) seems to be a random number? Any ideas what's missing? Using Excel 2002 btw.

 

[MintJulep]

The code you posted worked fine for me. Needed to add an End Sub at the end, otherwise didn't have to do a thing.

I've got Excel 2000.

 

[FredGarvin]

I have Excel 2003 and it worked fine. Like Mint said, had to add the end sub line.

 

[pugap]

The end sub is in my code (simply forgot to copy it to here), but it still doesn't explain why there is a random number generated by the strptr() function.

 

[analogkid2digitalman]

I have never seen nor used the StrPtr() function, but it looks and smells to me like it is a pointer. Thus it may be returning an address that points to the data. Depending on what memory is available at the time, then it may return what appears to be a 'random' address. If cancel is selected, the pointer never gets assigned, and thus the call returns zero.

Just a hunch...

Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.