Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vbCancel

Posted on 2010-08-13
7
Medium Priority
?
1,043 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:JP
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:JP
ID: 33433882
Sorry, remove the msgbox line you only need it the once with the answer = line
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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:JP
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

610 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