Application.InputBox - "Cancel" button

KerryFSA

New Member
Joined
Dec 5, 2018
Messages
49
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
That works for me with Q as Variant.
 

KerryFSA

New Member
Joined
Dec 5, 2018
Messages
49
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
What happens if you try
Code:
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

New Member
Joined
Dec 5, 2018
Messages
49
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
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
Code:
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

New Member
Joined
Dec 5, 2018
Messages
49
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
Which is something that you have already asked (and I thought resolved) on another site.
 

KerryFSA

New Member
Joined
Dec 5, 2018
Messages
49
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,178
Messages
5,442,850
Members
405,203
Latest member
Mira_Xcel

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top