Cancel an InputBox?

[Guest]

Sorry if this question has been asked before, but I did a search and I didn't
find anything.

In any case, I have a command button on my form that opens an InputBox in
order to prompt the user for input. On the InputBox are two buttons, "Ok"
and "Cancel." I would like to exit the procedure when a user clicks the
Cancel button, but my code is not working.

I tried the same code using a MsgBox and specifying the buttons as
vbOkCancel and it exited the procedure correctly, but for some reason it
doesn't seem to work with an InputBox.


Dim Record As String
Dim Message As String
Dim Response

Message = "What field would you like to count?"

Response = InputBox(Message)

If Response = vbCancel Then
Exit Sub
Else: Record = Response
End If

If someone could offer a suggestion as to how to get it to exit the
procedure I would appreciate it.

Thanks,

Chris

 

[Gman]

Try looking at what gets sent back from the InputBox when the user
clicks Cancel (or clicks the close X). You just get empty back.

Try
If Response = Empty then
Exit sub
Else

Or even LEN(response) = 0

It wouldn't do to be able to check using vbCancel from an InputBox since
this is also just the number 2. So if someone enters 2 into your
inputbox it would also be vbcancel. If that makes sense... try printing
vbCancel and vbOK etc. in debug.

HTH

 

[Guest]

Try this

Dim Record As String
Dim Message As String
Dim Response

Message = "What field would you like to count?"

Response = InputBox(Message)

If trim(Response) = "" Or IsNull(Response) Then ' Check for empty return
Exit Sub
Else
Record = Response
End If

 

[Guest]

Thanks guys, I tried your code and it allows me to cancel the procedure.

-Chris

 

[RoyVidar]

Ofer wrote in message

Try this

Dim Record As String
Dim Message As String
Dim Response

Message = "What field would you like to count?"

Response = InputBox(Message)

If trim(Response) = "" Or IsNull(Response) Then ' Check for empty return
Exit Sub
Else
Record = Response
End If

There's also the possibility of using the StrPtr function for this

If StrPtr(Response)=0 Then
MsgBox "Oh dear, Cancel ..."
ElseIf Response = "" then
MsgBox "This is OK, but without value ..."
Else
Record = Response
End If

but needing to differensiate between someone hitting Cancel
and someone hitting OK without entering any value isn't
something I do often.