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.

 

[Akuini]

Hi, KerryFSA
I can’t test it on Mac, but if clicking cancel will throw an error why not using an error handling, something like:
On Error GoTo skip:

And put “skip: “ in the end of the code.

 

[Fluff]

As I don't have a Mac, I can't test anything, but there are a number of differences between VBA on a PC & on a Mac.
The prompt not appearing may simply be a bug in the Mac version.

 

[mikerickson]

The type argument of the InputBox is 1. That means its expecting a numeric entry and will be outputting a number.

If the user presses Cancel, Application.Inputbox("prompt", type:=1) will return the numeric equivalent of False, ie. 0.

 

[Fluff]

With the code from post#7 The message box return "False"

 

[KerryFSA]

After much trial and error, I believe I have found the problem: the lines of text in my InputBox were simply too long to fit in the dialogue box and the InputBox was not even executing. For posting here, I used shorter lines to make the post easier to read. If I am correct, I apologize for wasting your time. With shorter lines, the output is "false" for cancel and I could alter the coding to end the sub. However, the apparent Mac quirk remains as noted above and i will not be able to use "Application InputBox" on the Mac. However, it may work in the Windows version of my program. Thank you all very much for replying; your answers will be useful in any event.

 

[Fluff]

Glad you've manage to sort it (after a fashion) & thanks for the feedback