• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1062
  • Last Modified:

vbCancel

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
1r3o2n8
Asked:
1r3o2n8
1 Solution
 
JPIT DirectorCommented:
You could try the following
if answer <> vbYes then exit sub
0
 
1r3o2n8Author Commented:
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
 
JPIT DirectorCommented:
Sorry, remove the msgbox line you only need it the once with the answer = line
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
byundtCommented:
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
 
alismadiCommented:
'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
 
byundtCommented:
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
 
JPIT DirectorCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now