PRINT macro: prevent error when canceling at InputBox level

Hi Guys,

I have a macro that asks the user what pages they would like to print on a particular sheet. In the example below, a button will be assigned to pages 1-5 on a sheet and thus the user has the option of printing from page 1 up to page 5. Any page number outside this range will result in an error message and the user will be asked to re-enter the end page number.

This is the code I pulled from this forum and partially modified:

Sub Print_Pages()
   
    Dim StartPage As Long
    Dim Endpage As Long
    Dim Page As Integer
    
    StartPage = 1
    
query:     Endpage = InputBox("From page 1 to...?")

    If Endpage <= 5 Then
    
    For Page = StartPage To Endpage
    
    ActiveSheet.PrintOut from:=Page, To:=Page, _
                             Copies:=1, Collate:=True
        
    Next
    
    Else
    
    MsgBox "invalid number"
    
    GoTo ask
    
    End If
    
End Sub

Thing is if "cancel" is chosen on the input box at any stage, I get an error.

I'm a little confused about how or where to place an 'exit sub' statement?

Or is there a more succinct macro that allows the user to input the page numbers of their choice?

Any suggestions please?

Thanks Guys.

Z.

It's because the Inputbox returns "" when Cancel or escape is used.

Change the variable type and check for that return value

Sub Print_Pages()
   
    Dim StartPage As Long
    Dim Endpage As Variant
    Dim Page As Integer
    
    StartPage = 1
    
query:     Endpage = InputBox("From page 1 to...?")

    If Endpage = "" Then Exit Sub
'...

You should also check for non numeric values.
Or use the Application.Input box which would allow you to restrict input to numbers

Hi,

Thanks Andy. For the benefit of others, my final code is as follows:

Sub Print_Pages()
   
    Dim StartPage As Long
    Dim Endpage As Variant
    Dim Page As Integer
    Dim answer As Integer
    
    StartPage = 1
    
query: Endpage = Application.InputBox("From page 1 to...?", "Print Options")
    
    If Endpage = "" Then Exit Sub
    If Endpage <= 5 Then
    For Page = StartPage To Endpage
    ActiveSheet.PrintOut from:=Page, To:=Page, Copies:=1, Collate:=True
    
    Next
    Else
    answer = MsgBox("Invalid number", vbExclamation + vbRetryCancel, "")
    If answer = vbCancel Then Exit Sub
    GoTo query
    End If
    
End Sub

Can you confirm that this isn't overly 'sloppy' in terms of coding? As a newbie I feel as though I could run into problems down the track if my code is scratchy.

The code is okay. Any changes would only be style/personal preference changes rather than to correct sloppy code

For restricting numeric entry in cells see Data Validation

For restricting numeric entry in input box see the Type argument for Application.Inputbox

If you need further help on either then start a new thread.