InputBox Function & Cancel

[Otto Moehrbach]

Excel 2002, WinXP
I am using the InputBox Function to get some info from the user.
I want to trap the instance of the user clicking the Cancel button.
Clicking on the Cancel button returns a zero-length string.
No input and clicking on the OK button also returns a zero-length string.
Is there a way to differentiate between the two actions?

I realize that I can use the InputBox Method and specify a type and thereby
force the user to input something. Also the Cancel button returns a False.
Thanks for your help. Otto

 

[Norman Jones]

Hi Otto,

You can use the StrPtr function:

Sub Tester()
Dim strInput As String

strInput = InputBox("Complete this quotation", _
"InputBox Demo", _
"Friends, Romans and Countrymen")
If StrPtr(strInput) = 0 Then
MsgBox "You pressed Cancel"
Else
If Len(strInput) = 0 Then
MsgBox "OK was pressed but no entry was made."
Else
MsgBox "Your entry was: " & strInput
End If
End If
End Sub

For information about StrPtr see Karl Peterson's site:

http://www.mvps.org/vb/index2.html?tips/varptr.htm

 

[Harald Staff]

Hi Otto

Application.Inputbox will require certain input types and return False on
cancel. If your last chapter is, as I understand it, saying "I know that and
it's still no good" then no, no distinction between them, you may have to
build a userform and code this yourself. Which is nothing but great fun to
do.

HTH. Best wishes Harald

 

[Harald Staff]

Hi Norman

I stand corrected. This was good, thanks.

Best wishes Harald

 

[Otto Moehrbach]

Thanks Norman. I'll check it out in the morning. Thanks for your help.
Otto