Application.InputBox - "Cancel" button

[KerryFSA]

I am having trouble coding a macro in the case where the response to an InputBox request is the "Cancel" button.
As I understand it, the cancel button returns the value "False". The following code results in "Type Mismatch" error 13 at the "If Q = False" line. Q has been defined as a variant. In short, if "Cancel" is clicked, I want he sub to end.

Q = Application.InputBox(Prompt:="Please enter a number from 1 to 3 to indicate how you want the lists sorted:" & vbCr & _
"1 - alphabetically by name;" & vbCr & _
"2 - alphabetically city;" & vbCr & _
"3 - numerically by location number" & vbCr & _
" After number entered, click 'OK.'", Title:="SORT CRITERIA", Type:=1)
' If "cancel" is clicked:
If Q = False Then
ActiveWorkbook.Sheets(sh).Range("D10").Select
Application.CutCopyMode = False
Exit Sub
End If
Other code and the sort routines follow.

Thanks very much for your help.

 

[jim may]

What if instead of If Q = False, change it to If Q = vbCancel

 

[Fluff]

That works for me with Q as Variant.

 

[KerryFSA]

Further to my original post above: Obviously, there is something wrong with the InputBox design. When I insert
MsgBox Q
I get the same error. If I take out the the check for "False", the following also results in an error:
If Q <= 0 Or Q > 4 Or Q = "" Then .... more code follows.

Using vbCancel doesn't work either. Q doesn't seem to be picking up a value correctly.

BTW I'm using Excel for MAC, latest version.

 

[Fluff]

What happens if you try

Sub chk()
MsgBox "|" & Application.InputBox(prompt:="Please enter a number from 1 to 3 to indicate how you want the lists sorted:" & vbCr & _
"1 - alphabetically by name;" & vbCr & _
"2 - alphabetically city;" & vbCr & _
"3 - numerically by location number" & vbCr & _
" After number entered, click 'OK.'", Title:="SORT CRITERIA", Type:=1) & "|"
End Sub

 

[KerryFSA]

Fluff, Thanks for the suggestion. Your Sub chk() returns a value of false. I don't know the convention of the | symbol. Nor do I understand how to use the value of False, since it doesn't appear to be assigned to a variable that I can use in my sort routines. Can you elaborate, please. Thanks

 

[Fluff]

The pipe (|) was just a delimiter, so you should have seen |False|, but if for some reason it was returning nothing (ie "") then you would have seen ||

As it is returning False, then this should work

Sub chk()
   Dim Q As Variant
   Q = Application.InputBox(prompt:="Please enter a number from 1 to 3 to indicate how you want the lists sorted:" & vbCr & _
      "1 - alphabetically by name;" & vbCr & _
      "2 - alphabetically city;" & vbCr & _
      "3 - numerically by location number" & vbCr & _
      " After number entered, click 'OK.'", Title:="SORT CRITERIA", Type:=1)
   MsgBox Q
End Sub

 

[KerryFSA]

Further: it appears that in my original coding, the InputBox is being bypassed altogether. If I simplify the InputBox prompt to:

Q = Application.InputBox(Prompt:="Please enter a number from 1 to 4 to indicate how you want the Retailer lists sorted:", Title:="SORT CRITERIA", Type:=3)

The correct result is obtained if I click on Cancel. But the Prompt doesn't appear at all in the InputBox.

 

[Fluff]

Which is something that you have already asked (and I thought resolved) on another site.

 

[KerryFSA]

No, it was never "resolved". It was answered by advising me to use the function not the method.
The function doesn't offer all that I want in my current project. The answer also didn't satisfactorily answer why the prompt doesn't show up.
(Pretty easy for a Windows user to say "it must be a Mac problem".) Not being satisfied then, it was worth asking again.
If you've got a better answer, kindly pass it on. Thank you for your other replies.

I very much appreciate the users of Mr Excel - replies are very fast and very helpful.