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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. 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
56,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
56,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
56,562
Office Version
  1. 365
Platform
  1. 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,129,574
Messages
5,637,163
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top