?
Solved

vbCancel

Posted on 2010-08-13
7
Medium Priority
?
1,027 Views
Last Modified: 2012-05-10
The code below with the variable. "answer" doesn't work .  When cancel is pressed, the box comes up again.  On second try it works.  Plese help me resolve the problem.  
CopiesTrap:        'comes here from below if copies number is out of range 1 - 5.


   
    MyInput = InputBox("How Many Copies To Print?  Must Be A Single Digit (1-5). ", "Input Box", "Enter 1-5") 'Highlight "InputBox" and press F1 for help.
   
   
    Application.DisplayAlerts = True   ' so one can see messages.

              If MyInput = "" Or MyInput < 1 Or MyInput > 5 Then
             
             
             
Dim Answer As Integer

MsgBox "Choose OK To Continue Or Cancel To Abort", vbOKCancel

Answer = MsgBox("Choose OK to continue or Cancel to abort", vbOKCancel)  'line is not an executable statement

If Answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
             
         
             
                    'MsgBox "Enter The Correct Number Of Copies."
    GoTo CopiesTrap  ' if # of copies does not fall within parameters; else continues.
             
         End If
0
Comment
Question by:1r3o2n8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 14

Expert Comment

by:peetjh
ID: 33433617
You could try the following
if answer <> vbYes then exit sub
0
 

Author Comment

by:1r3o2n8
ID: 33433848
Still the same problem.  When I click either OK or Cancel, the msg box quickly reappears. When I click either box the second time, both work OK.  Could there be a end sub or end if problem?  Thanx.
0
 
LVL 14

Expert Comment

by:peetjh
ID: 33433882
Sorry, remove the msgbox line you only need it the once with the answer = line
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 81

Expert Comment

by:byundt
ID: 33434362
I'd rewrite your code as shown in the snippet. By using the Val function, you convert text and blanks into 0--which is then trapped by the If block.

By testing for the return value from your MsgBox on the same line, you eliminate the need for the Answer variable and the confusion it was causing you.

Brad
Sub Test()
Dim MyInput As Long
MyInput = Val(InputBox("How Many Copies To Print?  Must Be A Single Digit (1-5). ", _
    "Input Box", "Enter 1-5")) 'Highlight "InputBox" and press F1 for help.
Application.DisplayAlerts = True   ' so one can see messages.
If MyInput < 1 Or MyInput > 5 Then
    If MsgBox("Choose OK to continue or Cancel to abort", vbOKCancel) = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
    GoTo CopiesTrap  ' if # of copies does not fall within parameters; else continues.
End If
CopiesTrap:
End Sub

Open in new window

0
 
LVL 1

Accepted Solution

by:
alismadi earned 500 total points
ID: 33434373
'Use VAL function to make sure you convert the user input to number  
'(the result will be  DOUBLE type) you might wanna make sure its integer
'Use INT(myInput) to convert the double value to int in case the user entered 3.2, 4.1..etc

Application.DisplayAlerts = True   ' so one can see messages.

CopiesTrap:
MyInput = InputBox("How Many Copies To Print?  Must Be A Single Digit (1-5). ", "Input Box", "Enter 1-5") 'Highlight "InputBox" and press F1 for help.
 
If MyInput <> vbnullstring then            'vbNullString is when the user cancel the inputbox

  If val(MyInput) > 0 AND Val(MyInput) <=5 Then    'If the user click OK without anything the result is vbNullString
     
  Dim Answer As Integer

  If MsgBox("You have enter " & MyInput & " copies, Are you sure you want to continue?, vbYesNo)  = vbYes then
     '
     'Print order statement(s)
     '
  ELSE
     '
     'Cancelled (NO) Clicked, Optional MsgBox confirming cancelation
     '
     'MsgBox "Print order has been cancelled."
  END IF
 
             
ELSE

    'When the user cancel the inputbox, the inputbox will return the value "vbNullString"
    GoTo UserCancelled   'Or do nothing its up to u, Unless you want to show MsgBox saying you have cancelled the print order...
     
     ' Or you can exit sub...whichever works with the sequence of your app.

End If


userCancelled:            'Pass the input option
0
 
LVL 81

Expert Comment

by:byundt
ID: 33434435
By declaring MyInput as a Long, a value like 3.2 will be truncated to 3. No problem will occur--no matter whether the user enters a blank, text, a valid number or an invalid number.
0
 
LVL 14

Expert Comment

by:peetjh
ID: 33436237
Since points have become more of a challenge to get I just wanted to point out that the question was just to fix the answer variable and msgbox not working the way expected not to rewrite the entire method. I know that the advice I gave resolved the issue and even if my comment was not accepted it should have been an assist.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question