Solved

vbCancel

Posted on 2010-08-13
7
981 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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