Solved

vbCancel

Posted on 2010-08-13
7
1,011 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 125 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

729 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