+ Reply to Thread
Results 1 to 3 of 3

Return to InputBox based on Value AND vbYes/No

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Return to InputBox based on Value AND vbYes/No

    Hello,

    I have a pretty simple macro here for printing cells on a worksheet that has inputBoxes to ask for the info. My inputBox "EndJob" to ask for number of copies to print will not return to the inputbox based on quantity and vbYes or vbNo. I am pretty new at this and still learning so I have tried and tried to get this to work but cannot seem to figure it out. Here is the piece of code I have been working on from the complete code below. Any help is greatly appreciated!

    EndJob = Application.InputBox(vbNewLine & "Enter the TOTAL number of copies to print:", "TOTAL PRINTS", Type:=1)
    If EndJob >= 20 Then
    Response = MsgBox("Are you sure you want to print " & EndJob & " copies?", vbYesNo + vbCritical)
    
    ' this is where I am stuck
    If Response = vbNo Then  ' how do I return to inputBox to ask for a new quantity?
    
    If EndJob = 0 Then Exit Sub   'operator pressed cancel

    -------------------------------------------
    Code without IF statements


    
    Sub PrintJobSheet()
    
    Range("I9").ClearContents
    
    Dim Serial As String, StartJob As Long, EndJob As Long, Job As Long, aNum As String
    
    
    Do
    'ask for assembly number
    Assy = UCase(Application.InputBox(vbNewLine & "Enter the ASSEMBLY END-ITEM NUMBER: " & vbNewLine & vbNewLine & "*PRESS ENTER AFTER EACH ENTRY", "Assembly", Type:=2))
    Range("D7").Value = Assy
    Loop Until Len(Assy) > 2
    
    
    Do
    'ask for A Number
    aNum = UCase(Application.InputBox(vbNewLine & "Enter the A NUMBER: ", "A-Number", Type:=2))
    Range("D9").Value = aNum
    Loop Until Len(aNum) > 3
    
    
    Do
        Serial = Left(UCase(Application.InputBox(vbNewLine & "Enter beginning 'LETTER' of the SERIAL NUMBER:", "LETTER", Type:=2)), 1)
        If Serial = "FALSE" Then Exit Sub
    Loop Until Not IsNumeric(Serial)
    
    StartJob = Application.InputBox(vbNewLine & "Enter the 'FIRST SERIAL NUMBER' to be printed:", "S/N START", Type:=1)
    If StartJob = 0 Then Exit Sub    'operator pressed CANCEL
    
    EndJob = Application.InputBox(vbNewLine & "Enter the TOTAL number of copies to print:", "TOTAL PRINTS", Type:=1)
    
    If EndJob = 0 Then Exit Sub   'operator pressed cancel
    
    
            For Job = StartJob To StartJob + EndJob - 1
            Range("I9").Value = Serial & Job
            ActiveWindow.SelectedSheets.PrintOut
        Next Job
    
        Range("I9").ClearContents
        
        Call ClearRange
        
        ActiveWorkbook.Save
        Application.DisplayAlerts = False
        Application.Quit
        
    End Sub

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Return to InputBox based on Value AND vbYes/No

    You could try with this code:

       Do
          response = 0
          EndJob = Application.InputBox(vbNewLine & "Enter the TOTAL number of copies to print:", "TOTAL PRINTS", Type:=1)
       
          If EndJob >= 20 Then
             response = MsgBox("Are you sure you want to print " & EndJob & " copies?", vbYesNo + vbCritical)
          End If
    
          If EndJob = 0 Then Exit Sub   'operator pressed cancel
       Loop While EndJob >= 20 And response = vbNo
    Regards,
    Antonio

  3. #3
    Forum Contributor
    Join Date
    04-03-2012
    Location
    Washington State
    MS-Off Ver
    Excel 365
    Posts
    340

    Re: Return to InputBox based on Value AND vbYes/No

    Antonio - Thank You very much for your help.... this works exactly how I need it to. Much appreciated!

    -Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1